Only show data in both datasets

[#1]

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!

[MK_QSL]

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)

[#3]

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

[Gysbert_Wassenaar]

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

[#5]

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!

[MK_QSL · Accepted solution]

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)

[#7]

Great, Thanks a lot!!!

[#8]

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

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

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!

[MK_QSL]

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