Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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,
Hi,
Check with this
IF (Indication = 'Not Retrieved', 'NOT',IF (Indication = 'Retrieved', 'Retrieved','Missing')) as [NEW COLOM],
Celambarasan
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,
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
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,