topic Re: Match data from two tables in QlikView

Match data from two tables

Thu, 26 Jan 2012 14:14:03 GMT

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

Re: Match data from two tables

Miguel_Angel_Baeyens — Thu, 26 Jan 2012 15:07:54 GMT

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

Match data from two tables

Thu, 26 Jan 2012 15:51:09 GMT

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)

Malte

Re: Match data from two tables

Miguel_Angel_Baeyens — Thu, 26 Jan 2012 15:56:49 GMT

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

Match data from two tables

Fri, 27 Jan 2012 10:59:04 GMT

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

Match data from two tables

Fri, 27 Jan 2012 11:18:01 GMT

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);

Re: Match data from two tables

Miguel_Angel_Baeyens — Fri, 27 Jan 2012 11:28:13 GMT

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

Match data from two tables

Fri, 27 Jan 2012 12:05:30 GMT

Finally, now I got it to work.

Have a nice weekend and thanks again!

/ Malte

Match data from two tables

Tue, 14 Feb 2012 08:40:24 GMT

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

Re: Match data from two tables

Miguel_Angel_Baeyens — Tue, 14 Feb 2012 11:28:55 GMT

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

Match data from two tables

Wed, 22 Feb 2012 07:33:50 GMT

Sorry for late reply. It was what I was looking for. Thanks again

Re: Match data from two tables

Thu, 19 Apr 2012 08:08:35 GMT

Great response Miguel...this helped me as well

Cheers,

Byron

Re: Match data from two tables

farolito20 — Fri, 14 Dec 2012 23:18:46 GMT

but if are more than two fields, how execute exist function?

Re: Match data from two tables

Fri, 28 Mar 2014 08:09:27 GMT

Hi Miguel,

I am having data extracted from SQL table and I am matching it with an inline table to get a field.

But the null data is not coming in the chart. Please help me on this.

Below is my inline table

Start:

ADD LOAD * Inline [_HW_Code, _SW, _SW Name, _BUSINESS, _Domain, _SW Domain Desc, _L1, _L2

BB, RR, Services, Support, <NULL>, <NULL>, Services, Services

<NULL>, <NULL>, Others, Others, Others, Others, Others, Others

] ;

Below is my Dimension expression in sparkline chart

=if(HW=_HW_Code,if(Match([_L1],'Segment','Services','Software','Engineering','Technology','Support')>0,[_L1],'Others')
,If(IsNull(_HW_Code),'Others'))

Note:

'Supress when value is null' is NOT ticked, nor is 'Supress zero-values' or 'Supress Missing'.