topic Re: Only show data in both datasets in QlikView

Only show data in both datasets

Fri, 14 Feb 2014 12:15:30 GMT

Hi,

I linked two datasets together (or actually 6, but I turned 5 files into one with the JOIN formula). However, in dataset A are also files that are not in dataset B, and the other way around. I linked the files based on [PO Number], and most of my formulas are based on the this factor too (count([PO Number]); Count([On time])/[PO Number]). Now I want to do the following three things:

1) I want my formulas/graphs only to take data into account, which can be found in both files.

2) I want to make a table which shows which data is in dataset A, but not in dataset B

3) I want to make a table which shows which data is in dataset B, but not in dataset A

How do I adjust my script/formulas to do this?

Thanks!

Re: Only show data in both datasets

MK_QSL — Fri, 14 Feb 2014 12:19:42 GMT

You can create a flag for all your table in loading script.

TableA:

Load [PO Number],

A as Flag

From TableA:

Now In expression, use something like below..

SUM({<Flag = {'A'}>}Sales)

and

SUM({<Flag = {'B'}>}Sales)

Re: Only show data in both datasets

Fri, 14 Feb 2014 12:30:16 GMT

Hi Manesh,

So I gues this solves the problem for question 3 and 2, thanks

And for question 1, when I want to use only want data that is in both sets, I just do SUM({<Flag = {'A','B'}>}Sales)?

Re: Only show data in both datasets

Gysbert_Wassenaar — Fri, 14 Feb 2014 15:22:50 GMT

Something like this: SUM({<Flag = {'A'},Sales=p({<Flag={'B'}>}Sales)>}Sales)

Re: Only show data in both datasets

Fri, 14 Feb 2014 15:31:52 GMT

Hi Gysbert and Manish,

Thank you. I understand the set analysis part I will have to do after I have created a flag for all of my script. But I cannot manage to get the flag for the script...

Could one of you guys Help me with a very simple example of this? I tried to make a very simple script, but i still keep getting errors when loading it. So I start with this as a basis:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;€ -#.##0,00';
SET TimeFormat='h:mm:ss';
SET DateFormat='D-M-YYYY';
SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';
SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';
SET DayNames='ma;di;wo;do;vr;za;zo';

TableA:

LOAD [Carrier Name],
[PO Number]

FROM

~~(ooxml, embedded labels, table is Sheet1);~~

TableB:

LOAD Destination,
[PO Number]

FROM

~~(ooxml, embedded labels, table is Sheet1);~~

Could one of you guys add the lines i'm missing? When I understand this simple script, I can probably manage with my original data. Thanks!

Re: Only show data in both datasets

MK_QSL — Fri, 14 Feb 2014 15:35:00 GMT

Check enclosed file..

UPDATE ::::

Also, If you want SUM(Sales) where Customers are present in BOTH Table1 and Table2.. use below formula..

SUM({$<Flag = {'A'},Customer = P({<Flag = {'B'}>}Customer)>}Sales)+SUM({$<Flag = {'B'},Customer = P({<Flag = {'A'}>}Customer)>}Sales)

Re: Only show data in both datasets

Fri, 14 Feb 2014 15:40:48 GMT

Great, Thanks a lot!!!

Re: Only show data in both datasets

Mon, 17 Feb 2014 14:11:32 GMT

Hi Manisch and Gysbert,

It worked fine for me put the data right away in the script when I was working with the data of a few days, but now I got it running, I have to make the dashboard for the entire year. So I would like to Flag the data in the headers in my script. How do I do this? Once more my old script...

TableA:

LOAD [Carrier Name],
[PO Number]

FROM

~~(ooxml, embedded labels, table is Sheet1);~~

TableB:

LOAD Destination,
[PO Number]

FROM

~~(ooxml, embedded labels, table is Sheet1);~~

Join

TableC:

LOAD Destination,

[PO Number]

FROM

~~(ooxml, embedded labels, table is Sheet1);~~

Could one of you guys right how I can flag the entire dataset in a script like this? (so with only the link tot the data in the file, and not all values of the data itself)

Thanks!

Re: Only show data in both datasets

MK_QSL — Mon, 17 Feb 2014 14:41:23 GMT

TableA:

LOAD [Carrier Name],
[PO Number]

FROM

~~(ooxml, embedded labels, table is Sheet1);~~

TableB:

LOAD Destination,
[PO Number],

'B' as Flag

FROM

~~(ooxml, embedded labels, table is Sheet1);~~

TableC:

LOAD Destination,

[PO Number],

'C' as Flag

FROM

~~(ooxml, embedded labels, table is Sheet1);~~