Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

IF statement

I have a problem with building an IF statement.

the data is as follows:

ID      Indication          Date               (NEW COLOM)

61     Retrieved          01-01-2012     NOT

61     Retrieved          01-02-2012     NOT

61     Not Retrieved     01-03-2012     NOT

61     Not Retrieved     01-04-2012     NOT

So for a certain ID field i have two possible status depending on the time. I want to make a new field that says in every situation that there is data not retrieved.

IF (Indication = 'Not Retrieved', 'NOT', 'YES') as [NEW COLOM],

This will only give met Not where it says not retrieved. How do i get this for the retrieved records as well.

goal is to know which ID fields still have some missing values. I want to make this in the QlikView script.

Can anyone help me with this issue?

1 Solution

Accepted Solutions
Not applicable
Author

Hi Celambrasan,

thank you again for your comment. I evently fixed it (with some help )with the following script:

GroupingTabel:

Load ID,

                If(Only(Indication)=Retrieved,'No','Yes') AS Newkolom

Resident data

Group by ID;

Left join (data)

Load *

Resident GroupingTabel;

Drop GroupingTabel;

kind regards,

View solution in original post

4 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Check with this

     IF (Indication = 'Not Retrieved', 'NOT',IF (Indication = 'Retrieved', 'Retrieved','Missing')) as [NEW COLOM],

Celambarasan

Not applicable
Author

Hi Celambrasan,

Thank you for the reply, your answer almost covers it. However it gives the status 'retrieved' in the NEW COLOM where the indication is retrieved.

It should give me 'NOT' in the new colom because for that ID field there is an indication 'Not retrieved' anywhere in time. Example:

ID      Indication          Date               (NEW COLOM)

61     Retrieved          01-01-2012     NOT

61     Retrieved          01-02-2012     NOT

61     Not Retrieved     01-03-2012     NOT

61     Not Retrieved     01-04-2012     NOT

ID      Indication      Date                (NEW COLOM)

51     Retrieved    01-01-2012         YES

51     Retrieved    01-02-2012         YES

51     Retrieved     01-03-2012        YES

If for every date the indication is retrieved then it should give yes for the ID field.

Thanks in advance,

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Then try with this

      IF (Indication = 'Not Retrieved', 'NOT',IF (Indication = 'Retrieved', 'YES','Missing')) as [NEW COLOM],

OR

     Pick(Match(Indication,'Not Retrieved','Retrieved'),'NOT','YES')

Celambarasan

Not applicable
Author

Hi Celambrasan,

thank you again for your comment. I evently fixed it (with some help )with the following script:

GroupingTabel:

Load ID,

                If(Only(Indication)=Retrieved,'No','Yes') AS Newkolom

Resident data

Group by ID;

Left join (data)

Load *

Resident GroupingTabel;

Drop GroupingTabel;

kind regards,