Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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.
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
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
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
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
Many thank guys for your reply.
But How can I create a calendar state like my attachment ?
Regards,