Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a requirement where I need to compare two fields from one table with the field in another table and create the third field.
I have 4 fields in table 1 (i.e. [Assessment Name], [Field Type 1], [Field Type 2], [Field Type 3])
I have two fields in table 2 ((i.e. [Assessment Name] , [Field Type 1], [Field Type 2], [Field Type 3]).
Table 1 data:
Table 2 Data:
Output table:
Can anyone please guide me?
Thanks in advance!
Cheers,
Varun Reddy. K
Is this what you want?
Table:
LOAD [Assessment Name]
FROM
Table1.xlsx
(ooxml, embedded labels, table is Sheet1);
FinalTable:
LOAD 0 as Dummy
AutoGenerate 0;
For i = 1 to FieldValueCount('Assessment Name')
LET vAssessment = FieldValue('Assessment Name', $(i));
Table1:
LOAD [Assessment Name],
[Field Type 1],
[Field Type 2],
[Field Type 3]
FROM
Table1.xlsx
(ooxml, embedded labels, table is Sheet1)
Where [Assessment Name] = '$(vAssessment)';
LET vField1 = '[' & Peek('Field Type 1') & ']';
LET vField2 = '[' & Peek('Field Type 2') & ']';
LET vField3 = '[' & Peek('Field Type 3') & ']';
Concatenate (FinalTable)
LOAD [Assessment Name],
[Field Type 1] as $(vField1),
[Field Type 2] as $(vField2),
[Field Type 3] as $(vField3)
FROM
Table1.xlsx
(ooxml, embedded labels, table is Sheet2)
Where [Assessment Name] = '$(vAssessment)';
DROP Table Table1;
NEXT i
DROP Table Table;
Hi Varun -
2 things
1) Can you provide the sample data within Excel file so that we don't have to type all the data into a sample
2) Can you provide some more details on how you pick Field1, Field2, Field3...
Hi Sunny,
I have attached the excel file.
Table1:
Table2:
Sunny here in table1, for Assessment Name A we have Country Cd in Field Type1
In Table2, For Assessment B we have Country Cd in Field Type 3
I want Out put in this format:
Here Field Type1 from table 1 is compared with Field Type1 in table 2
Please let me know, if you still need explanation
Thanks,
Varun
Is this what you want?
Table:
LOAD [Assessment Name]
FROM
Table1.xlsx
(ooxml, embedded labels, table is Sheet1);
FinalTable:
LOAD 0 as Dummy
AutoGenerate 0;
For i = 1 to FieldValueCount('Assessment Name')
LET vAssessment = FieldValue('Assessment Name', $(i));
Table1:
LOAD [Assessment Name],
[Field Type 1],
[Field Type 2],
[Field Type 3]
FROM
Table1.xlsx
(ooxml, embedded labels, table is Sheet1)
Where [Assessment Name] = '$(vAssessment)';
LET vField1 = '[' & Peek('Field Type 1') & ']';
LET vField2 = '[' & Peek('Field Type 2') & ']';
LET vField3 = '[' & Peek('Field Type 3') & ']';
Concatenate (FinalTable)
LOAD [Assessment Name],
[Field Type 1] as $(vField1),
[Field Type 2] as $(vField2),
[Field Type 3] as $(vField3)
FROM
Table1.xlsx
(ooxml, embedded labels, table is Sheet2)
Where [Assessment Name] = '$(vAssessment)';
DROP Table Table1;
NEXT i
DROP Table Table;
Hi Sunny,
This is what I am looking for.
Thank you for the help
Cheers,
Varun Reddy