Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Flag Unique Customer IDs

Hi,

I am loading some Customer IDs in from 2 seperate databases and I want to identify which Customer ID's are contained in each table and which are not.

Hwo can I create flag script side so that I could correctly identify 1,3,5 as being duplicated but 2, 4 are not?

Cus_ID
1
2
3
4
5

Cus_ID
1
3
5

Thanks

Stuart

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: Flag Unique Customer IDs

There are different approaches possible, it may depend on the final data model you want to achieve.

For example:

LOAD Cus_ID

          'Table1' as Source

FROM Table1Source;

CONCATENATE

LOAD Cus_ID,

          'Table2' as Souce,

          If(Exists(Cus_ID),1)  as DuplicateFlag

FROM Table2Source;

This will flag the duplicates, but only in the second table records (still you are able to filter the Cus_ID accordingly).

6 Replies
vikasmahajan
Not applicable

Re: Flag Unique Customer IDs

Cust:

Load

Cus_ID , Flag

1,A

2,A

.

Concatenate

Cus_ID , Flag

1,B

3,B

5,B

Then Flag can be use in set analysis.

Vikas

swuehl
Not applicable

Re: Flag Unique Customer IDs

There are different approaches possible, it may depend on the final data model you want to achieve.

For example:

LOAD Cus_ID

          'Table1' as Source

FROM Table1Source;

CONCATENATE

LOAD Cus_ID,

          'Table2' as Souce,

          If(Exists(Cus_ID),1)  as DuplicateFlag

FROM Table2Source;

This will flag the duplicates, but only in the second table records (still you are able to filter the Cus_ID accordingly).

er_mohit
Not applicable

Re: Flag Unique Customer IDs

Hi

Try This.

Copy the same to your script and reload.

Table_Missing:

LOAD  *  Inline [

Cust_Id

1

3

5

];

Table_All:

LOAD if(Exists(Cust_Id),1,0) as Flag, * Inline [

Cust_Id

1

2

3

4

5

];

Not applicable

Re: Flag Unique Customer IDs

Thanks Stefan - is there any way to extended this method so that you can see the dupes on both sets of records?

swuehl
Not applicable

Re: Flag Unique Customer IDs

As said, there are multiple possible solution, e.g one without exists():

Table:

LOAD Cus_ID

          'Table1' as Source

FROM Table1Source;

CONCATENATE

LOAD Cus_ID,

          'Table2' as Souce

FROM Table2Source;

LEFT JOIN (Table)

LOAD Cus_ID,

          If(Count(DISTINCT Source)>1, 'Duplicate','No Duplicate') as Flag

RESIDENT Table

GROUP BY Cus_ID;

Not applicable

Re: Flag Unique Customer IDs

thanks - simple but genius