Compare two fields and update third if applicable

[varunreddy]

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

[sunny_talwar]

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...

[varunreddy]

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

[sunny_talwar · Accepted solution]

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;

[varunreddy]

Hi Sunny,

This is what I am looking for.

Thank you for the help

Cheers,

Varun Reddy