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: 
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
MVP
MVP

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).

View solution in original post

6 Replies
vikasmahajan

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

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
swuehl
MVP
MVP

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
Master II
Master II

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
Author

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

swuehl
MVP
MVP

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
Author

thanks - simple but genius