Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
simontouyet
Contributor
Contributor

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
Partner

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
Partner

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

zebhashmi
Specialist
Specialist

Maybe you can try with Hash128