topic Re: Crossing data with condition in App Development

Crossing data with condition

victor_greffet — Wed, 01 Nov 2017 10:12:41 GMT

Hi community !

I'm looking for a way to merge 2 columns depending on the title, I mean :

What I have :

Document title	BaselineDate1	BaselineDate2
Document1	date
Document2	date
Document 3	date
....		date
....		date
....		date
....	date
Document n-1
Document n		date

What I want :

Document title	BaselineDate1	BaselineDate2	BaselineDate
Document1	date		date
Document2	date		date
Document 3	date		date
....		date	date
....		date	date
....		date	date
....	date		date
Document n-1			'empty'
Document n		date	date

Knowing that when you have a date in BaselineDate1, BaselineDate2 is empty.

Furthermore it would be nice to have 'empty' for documents without date and keep the column BaselineDate1 and BaselineDate2 in my DATA

I tried with a left join and an if condition but without success,

Do you have an idea ?

Thanks !!

Victor

Re: Crossing data with condition

antoniotiman — Wed, 01 Nov 2017 10:20:24 GMT

May be this

LOAD [Document title],
BaselineDate1,
BaselineDate2,
If(Len(Trim(BaselineDate1)) > 0,BaselineDate1,If(Len(Trim(BaselineDate2)) > 0,BaselineDate2,'empty')) as BaselineData
FROM
https://community.qlik.com/message/1373060
(html, codepage is 1252, embedded labels, table is @1);

Regards,

Antonio

Re: Crossing data with condition

kenphamvn — Wed, 01 Nov 2017 10:24:08 GMT

Did you try left join and If condition in the one statement?

Please try this

1: Using left join to merger 2 table in to one with 2 column BaselineDate1, BaselineDate2

2: using if condition

if(len(BaselineDate1)>0,BaselineDate1,
if(len(BaselineDate2)>0,BaselineDate2,'Empty')) as BaselineDate

Regards

Re: Crossing data with condition

victor_greffet — Thu, 02 Nov 2017 09:01:46 GMT

It work too,

Many thanks,

Regards