Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
gyuvaraj
Contributor II
Contributor II

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

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:

gyuvaraj_1-1603965068628.png

 

Expected Output:

gyuvaraj_2-1603965122260.png

 

Regards,

Yuvaraj

 

 

Labels (1)
1 Solution

Accepted Solutions
edwin
Master II
Master II

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

View solution in original post

4 Replies
villegasi03
Creator
Creator

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

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

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

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