How to create a flag with same value in the loading script

[jim_chan]

Hi there, 

i have a problem here. i need to write this in loading script, as a flag/dimension. 

My expected result, when i add a TYPE dimension, it will help me to differentiate which ID number is 'positive with same ID' and 'Not the same'.

This is the ID number Group data which indicate which ID number is in the same ID group.

I have attached a sample qvw file, please help me out!

Rgds

Jim

[jim_chan]

hello anyone?

[edwin]

the requirement is unclear.  with these two tables, how do you identify which ones are positive line with same ID?
 what are the rules?

[jim_chan]

example ID number 12345 & 135789, this 2 numbers are in the same ID_GROUP 10001. 

[MayilVahanan · Accepted solution]

Hi @jim_chan 

Try like below

Temp:
LOAD * INLINE [
ID_GROUP, ID_NUMBER
10001, 12345
99999, 678910
10001, 135789
];

Join
LOAD * INLINE [
ID_NUMBER,COUNTRY,COLOR
12345, US,BLUE
678910, JAPAN,RED
135789, ITALY,ORANGE
];

Join
Load *, if(Count = 1, 'Not Same', 'positive with same ID') as TYPE;
Load ID_GROUP, Concat(ID_NUMBER, ',') as Group, Count(Distinct ID_NUMBER) as Count Resident Temp
group by ID_GROUP;