topic Re: Compare two fields and update third if applicable in QlikView

Compare two fields and update third if applicable

varunreddy — Thu, 01 Sep 2016 14:42:36 GMT

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

Re: Compare two fields and update third if applicable

sunny_talwar — Thu, 01 Sep 2016 14:45:52 GMT

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

Re: Compare two fields and update third if applicable

varunreddy — Thu, 01 Sep 2016 15:11:30 GMT

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

Re: Compare two fields and update third if applicable

sunny_talwar — Thu, 01 Sep 2016 21:23:47 GMT

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;

Re: Compare two fields and update third if applicable

varunreddy — Fri, 02 Sep 2016 01:09:24 GMT

Hi Sunny,

This is what I am looking for.

Thank you for the help

Cheers,

Varun Reddy