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

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

gyuvaraj — Fri, 15 Nov 2024 23:43:10 GMT

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

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

villegasi03 — Thu, 29 Oct 2020 15:03:16 GMT

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.

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

edwin — Thu, 29 Oct 2020 15:07:59 GMT

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

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

villegasi03 — Thu, 29 Oct 2020 15:09:06 GMT

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

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

villegasi03 — Thu, 29 Oct 2020 15:10:55 GMT

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