Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlikuser225
Contributor III
Contributor III

Calculated if condition

Hi All,

Can anyone please help me with below problem.

I have a table like below

 Qtr    ID      Weight     Flag1   Flag2     

Q1    123       10              -         Y         

Q1    123        20            -          N         

Q1    234        20            Y          -     

Q1    345        50            -           -

 

I want to show the table like below when Flag1 is Y

Qtr    ID      Weight     Flag1   Flag2     

Q1    123       10              -         Y         

Q1    234        20            Y          -     

Q1    345        50            -           -

 

Qtr    ID      Weight     Flag1   Flag2     

Q2    123       10              -         Y         

Q2    123        20            -          N         

Q2    234        50            N          -     

Q2    345        50            -           -

I want to show the table like below when Flag1 is N

Qtr    ID      Weight     Flag1   Flag2     

Q2    123        20            -          N         

Q2    234        50            N          -     

Q2    345        50            -           -

 

Thanks in Advance

Labels (3)
1 Solution

Accepted Solutions
Qrishna
Master
Master

try below:

d1:
load * inline [
Qtr,ID,Weight,Flag1,Flag2
Q1,123,10,,Y
Q1,123,20,,N
Q1,234,20,Y,
Q1,345,50,,
];

Qualify *;
Load *
Resident d1
where (Flag1 = 'Y' and Flag2 <> 'N') OR
(Flag1 <> 'N' and Flag2 = 'Y') OR
(len(trim(Flag1)) = 0 and len(trim(Flag2)) = 0);

//------------------------------------------------------------------------//
d2:
load * inline [
Qtr,ID,Weight,Flag1,Flag2
Q1,123,10,,Y
Q1,123,20,,N
Q1,234,50,N,
Q1,345,50,,
];


Load *
Resident d2
where (d2.Flag1 = 'N' and d2.Flag2 <> 'Y') OR
(d2.Flag1 <> 'Y' and d2.Flag2 = 'N') OR
(len(trim(d2.Flag1)) = 0 and len(trim(d2.Flag2)) = 0);
Unqualify *;

 

2489178 - Calculated if condition.PNG

 

you can remove Qualify *; statement and delete the source tables if you dont need them.

View solution in original post

2 Replies
Aditya_Chitale
Specialist
Specialist

What logic are you using while showing rows with same ID ?

For eg,  in 1st table, you have 2 rows with same ID '123' with weights 10 & 20.  But you want to show only the row with weight 10.

But in 2nd case, you have shown row with weight 20. what criteria are you setting for the expected output ?

 

Regards,

Aditya

Qrishna
Master
Master

try below:

d1:
load * inline [
Qtr,ID,Weight,Flag1,Flag2
Q1,123,10,,Y
Q1,123,20,,N
Q1,234,20,Y,
Q1,345,50,,
];

Qualify *;
Load *
Resident d1
where (Flag1 = 'Y' and Flag2 <> 'N') OR
(Flag1 <> 'N' and Flag2 = 'Y') OR
(len(trim(Flag1)) = 0 and len(trim(Flag2)) = 0);

//------------------------------------------------------------------------//
d2:
load * inline [
Qtr,ID,Weight,Flag1,Flag2
Q1,123,10,,Y
Q1,123,20,,N
Q1,234,50,N,
Q1,345,50,,
];


Load *
Resident d2
where (d2.Flag1 = 'N' and d2.Flag2 <> 'Y') OR
(d2.Flag1 <> 'Y' and d2.Flag2 = 'N') OR
(len(trim(d2.Flag1)) = 0 and len(trim(d2.Flag2)) = 0);
Unqualify *;

 

2489178 - Calculated if condition.PNG

 

you can remove Qualify *; statement and delete the source tables if you dont need them.