Match data from two tables

[#1]

Hello.

Would very much appreciate help with comparing data in two tables.

Table 1 (data in this table is static)

Table1
Green
Red
Yellow

Table 2 (data from this table is non static)

Table2
Red
Green

The result I'm looking for after comparing these tables would then look like this

Table1	Data_check
Green	Yes
Red	Yes
Yellow	No

Best Regards

Malte

[Miguel_Angel_Baeyens]

Hi Malte,

Working with the Exists() should do.

Table2Dynamic:
LOAD * INLINE [
Field
Red
Green
];
Table1:
LOAD *,
     If(Exists(Field), 'Yes', 'No') AS DataCheck INLINE [
Field
Green
Red
Yellow
];

Hope that helps.

Miguel

[#3]

Thank you for your answer.

The real Table 1 lists app 2000 uniqe data items. Is it still possible to use INLINE like presented in your answer.

(I have never used INLINE)

BR

Malte

[Miguel_Angel_Baeyens]

Hi Malte,

I'm using INLINE load just with a few values. You can use as many sources as you need in your script, a SQL SELECT from a database and an INLINE like above, if needed. In any case, you need to load first the date to be checked, and in the second case the large table that will have the conditional field depending on the values already loaded.

For what it's worth, INLINE means that the field names and values are hardcoded, but they are very useful to show how QlikView behaves with a few records.

Hope that makes sense. If don't, please elaborate or upload your script to adapt it or suggest more accurately.

Miguel

[#5]

Thanks for your patience Miguel.

Can you help me with the syntax if I get the data from two excel tables as below?

LOAD Tabel_1
FROM

(ooxml, embedded labels, table is Sheet1);

LOAD Tabel_2
FROM

(ooxml, embedded labels, table is Sheet1);

/Malte

[#6]

Try this:

table_1:

LOAD

    colour

FROM

(ooxml, embedded labels, table is Sheet1);

join (table_1)

LOAD

    colour,

    If(Exists(colour), 'Yes', 'No') as Data_check

FROM

(ooxml, embedded labels, table is Sheet1);

[Miguel_Angel_Baeyens · Accepted solution]

Malte,

Assuming the Table2 is the table that needs the conditional, the syntax would look like this

Tabel_2: // name of the table in QlikView
LOAD * // * means all fields, you can set the list or take only those that are of your interest
FROM
(ooxml, embedded labels, table is Sheet1);
Tabel_1:
LOAD *, // all fields from the excel file
     If(Exists(Field), 'Yes', 'No') AS DataCheck // new field added manually in the script
FROM
(ooxml, embedded labels, table is Sheet1);

The Exists() line reads as "if the value of Field has been already loaded (in this case, in Tabel_1) then store the string 'Yes' into a new field called DataCheck, and otherwise, store the string 'No'"

Hope this makes more sense now.

Miguel

[#8]

Finally, now I got it to work.

Have a nice weekend and thanks again!

/ Malte

[#9]

I still struggle with my data match.

Is it possible to use solution above also if you want to check "if exist" in source table with multiple tables?

Table1

Green

Red

Yellow

Table2

Green

Table3

Yellow

Desired result:

               Table2     Table3

Green         Yes        No    

Red            No          No

Yellow        No          Yes

[Miguel_Angel_Baeyens]

Hi Malte,

Consider the following script where you first load the tables with the values to be checked then the big table (Table1 in your example) with the values to check. The following should do. Note that the Exists() function gets two parameters, the first is the field already loaded to be checked, the second is the field from the current table.

Table2:
LOAD * INLINE [
Color2
Green
];
Table3:
LOAD * INLINE [
Color3
Yellow
];
Table1:
LOAD *, 
     If(Exists(Color2, Color1), 'Yes', 'No') AS Exists_In_Table2, 
     If(Exists(Color3, Color1), 'Yes', 'No') AS Exists_In_Table3 
INLINE [
Color1
Green
Red
Yellow
];

Hope that helps.

Miguel