Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

simontouyet
New 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?

Tags (2)
1 Solution

Accepted Solutions
Partner
Partner

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

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

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

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
Valued Contributor

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

Maybe you can try with Hash128