Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have one table with data. That table can have a same customer in multiple times with a different type. For example, I can have a customer have 2 rows of data in my excel file like this:
CustomerID | Quarter | Type |
---|---|---|
123456 | Q1 FY19 | New |
123456 | Q1 FY19 | Old |
I have been trying to find a workaround using exists() not exists() etc. so that when I load the table, I can have it check if the same customer is found in the table but containing a different Type (but for the same Quarter). The end result should be like this when loaded:
CustomerID | Quarter | Type | Duplicate with different type was found |
---|---|---|---|
123456 | Q1 FY19 | New | Yes |
123456 | Q1 FY19 | Old | Yes |
So the script should recognise that lines 1 and 2 are identical in CustomerID and Quarter but different in Type and put a "Yes".
Using Exists() and loading the table twice, I am able to check if the exact same record exists but I am not able to also specify that the type must be different.
How can this be achieved in a QlikSense load script?
Table1:
load * inline [
CustomerID, Quarter, Type
12345,Q1 FY19,Old
12345,Q1 FY19,New
12345,Q2 FY19,New
12222,Q2 FY19,Old
];
Table2:
load distinct CustomerID&'_'&Quarter as key, count(distinct Type) as cCount resident Table1 group by CustomerID,Quarter;
Table3:
load *,
if (lookup('cCount','key',CustomerID&'_'&Quarter,'Table2')>1,'Yes') as Dupe;
load CustomerID,Quarter,Type resident Table1;
drop table Table1,Table2;
Table1:
load * inline [
CustomerID, Quarter, Type
12345,Q1 FY19,Old
12345,Q1 FY19,New
12345,Q2 FY19,New
12222,Q2 FY19,Old
];
Table2:
load distinct CustomerID&'_'&Quarter as key, count(distinct Type) as cCount resident Table1 group by CustomerID,Quarter;
Table3:
load *,
if (lookup('cCount','key',CustomerID&'_'&Quarter,'Table2')>1,'Yes') as Dupe;
load CustomerID,Quarter,Type resident Table1;
drop table Table1,Table2;
Maybe you can try with Hash128