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

How can I create field from multiple dates ?

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,

1 Solution

Accepted Solutions
MarcoWedel

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

View solution in original post

6 Replies
ronnywaage
Contributor III
Contributor III

Hi

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.

cwolf
Creator III
Creator III

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

sunny_talwar

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

MarcoWedel

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

MarcoWedel

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

Not applicable
Author

Many thank guys for your reply.

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

Regards,