Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: How can I create field from multiple dates ?

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

6 Replies
ronnywaage
New Contributor III

Re: How can I create field from multiple dates ?

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.

chrwolf64
Contributor III

Re: How can I create field from multiple dates ?

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 ?

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 ?

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 ?

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

Re: How can I create field from multiple dates ?

Many thank guys for your reply.

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

Regards,

Community Browser