topic IF statement in QlikView

IF statement

Tue, 17 Apr 2012 14:47:19 GMT

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?

IF statement

CELAMBARASAN — Tue, 17 Apr 2012 14:50:44 GMT

Hi,

Check with this

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

Celambarasan

IF statement

Wed, 18 Apr 2012 07:53:32 GMT

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,

IF statement

CELAMBARASAN — Wed, 18 Apr 2012 08:48:23 GMT

Hi,

Then try with this

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

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

Celambarasan

IF statement

Thu, 19 Apr 2012 08:56:56 GMT

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,