Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Checking if a table has a row with the same customer name but a different value in another field

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:

CustomerIDQuarterType
123456Q1 FY19New
123456Q1 FY19Old

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:

CustomerIDQuarterTypeDuplicate with different type was found
123456Q1 FY19NewYes
123456Q1 FY19OldYes

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?

1 Solution

Accepted Solutions
stascher
Partner - Creator II
Partner - Creator II

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;

View solution in original post

2 Replies
stascher
Partner - Creator II
Partner - Creator II

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;

zebhashmi
Specialist
Specialist

Maybe you can try with Hash128