How to compare three field values and load the data into report

[gyuvaraj]

Hi,

I have ID value with duplicates, here need to load the highlighted data into qlik. For example, load the unique ID's into qlik with primary and secondary are having value or not.

Scenarios:

unique id with primary and secondary having both values(both)

unique id with primary value and secondary having no value(primary only)
unique id no primary value and secondary having value (secondary only)

unique id but no values in primary and secondary(both has no values)

tried like this:

if(Acc_type = 'J' and len(primary) >0 and len(secondary)>0, ID,

if(Acc_type = 'J' and len(primary) =0 and len(secondary)=0, ID,

if(Acc_type = 'J' and len(primary) >0 and len(secondary)=0, ID,

if(Acc_type = 'J' and len(primary) =0 and len(secondary)>0, ID))))

but not working as expected

Input table:

Expected Output:

Regards,

Yuvaraj

[villegasi03]

what results did you get from what you tried. BTW, if your logic for the IF statements were the opposite of what you have the statement would be shorter.

[edwin · Accepted solution]

i would load the data three times, once for the rows that have both metrics populated, 2nd to load any metric populated, 3rd rows where no metrics are populated.  this way you ensure the IDs are unique:

data: load Id, Id as tmpId, Acc, Primary, Secondary from <WHEREEVER> where not isnull(Primary) and not isnull(Secondary);

concatenate(data) load Id, Id as tmpId, Acc, Primary, Secondary from <WHEREEVER> where (not isnull(Primary) or not isnull(Secondary)) and not exists(tmpId, Id);

concatenate(data) load Id, Id as tmpId, Acc, Primary, Secondary from <WHEREEVER> where (isnull(Primary) and  isnull(Secondary)) and not exists(tmpId, Id);

drop field tmpId;

this assumes that when the field is blank its a null, if it is a space, check for spaces or len()>0

[villegasi03]

if you are loading this in your script if you want to separate the statements in OR statements. 

Load

ID,

primary,

secondary

From InputTable

WHERE (Acc_type = 'J' and len(primary) >0 and len(secondary)>0) OR

(Acc_type = 'J' and len(primary) =0 and len(secondary)=0) OR

(Acc_type = 'J' and len(primary) >0 and len(secondary)=0) OR

(Acc_type = 'J' and len(primary) =0 and len(secondary)>0);

[villegasi03]

doing that way will take care of empty strings. Good call