topic Re: How can I create field from multiple dates ? in QlikView

How can I create field from multiple dates ?

Fri, 06 Nov 2015 21:23:35 GMT

Hi all,

I have 5 dates. DATE_1 .. DATE_7.

I want to create field like that contains 12467

IF DATE_1 NOT NULL 1

IF DATE_2 NOT NULL 2

IF DATE_4 NOT NULL 4

IF DATE_6 NOT NULL 6

IF DATE_7 NOT NULL 7

Exemple :

STORE_ID , DATE_1, DATE_2, DATE_4, DATE_6, DATE_7

XXXX, 12/04/2015, 14/05/2015,,18/06/2015,23/06/2015

I want to create field STORE_STATE that contains 4 values : 1267

53662LT001,16/12/2015,06/10/2015,,, : the field state_store contains 12

Thank you for your help.

Regards,

Re: How can I create field from multiple dates ?

ronnywaage — Fri, 06 Nov 2015 22:21:29 GMT

I am not sure what you are trying to acheive based on your script. But if it is what you say in the text you can try this:

QUALIFY *;

TEMP:

NoConcatenate

Replace LOAD *,

IF(Not IsNull(DATE_1),1) &

IF(Not IsNull(DATE_2),2) &

IF(Not IsNull(DATE_4),4) &

IF(Not IsNull(DATE_6),6) &

IF(Not IsNull(DATE_7),7)

AS NEW_STATE_STORE

Resident Sheet1$;

UNQUALIFY *;

Since I have not access to your source file I just added the code above and took a partial reload based on the data you had in the app. It seems to work. You don't need the qualify commands nor the Replace load.

Attached is the code you can test with a partial reload.

Re: How can I create field from multiple dates ?

cwolf — Fri, 06 Nov 2015 22:32:38 GMT

if(not isNull(DATE_1),1) & if(not isNull(DATE_2),2) & if(not isNull(DATE_4),4) & if(not isNull(DATE_6),6) & if(not isNull(DATE_7),7) as NewField

Re: How can I create field from multiple dates ?

sunny_talwar — Fri, 06 Nov 2015 23:59:18 GMT

Using Ronny's Code, you can also do this if Dates are not true nulls and may have some invisible blank spaces in them:

If(Len(Trim(DATE_1)) > 0, 1) &

If(Len(Trim(DATE_2)) > 0, 2) &

If(Len(Trim(DATE_4)) > 0, 4) &

If(Len(Trim(DATE_6)) > 0, 6) &

If(Len(Trim(DATE_7)) > 0, 7) as NEW_STATE_STORE

Re: How can I create field from multiple dates ?

MarcoWedel — Sat, 07 Nov 2015 01:00:57 GMT

Hi,

maybe also possible:

If(DATE_1,1)&If(DATE_2,2)&If(DATE_4,4)&If(DATE_6,6)&If(DATE_7,7) as STORE_STATE;

hope this helps

regards

Marco

Re: How can I create field from multiple dates ?

MarcoWedel — Sat, 07 Nov 2015 01:03:46 GMT

or more generic:

table2:
CrossTable (colnam, colval,2)
LOAD STORE_ID,
    DATE,
    DATE_1,
    DATE_2,
    DATE_4,
    DATE_6,
    DATE_7
Resident table1;

table3:
LOAD STORE_ID,
    DATE,
    Concat(SubField(colnam,'_',2)) as STORE_STATE
Resident table2
Where Len(colval)
Group By STORE_ID,DATE;

DROP Table table2;

hope this helps

regards

Marco

Re: How can I create field from multiple dates ?

Sat, 07 Nov 2015 10:53:54 GMT

Many thank guys for your reply.

But How can I create a calendar state like my attachment ?

Regards,