Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

agf
New Contributor II

Replace specific cell IF

Hi Experts,

I have another issue with my app. The scenario seems to be complicated at least from a newbie perspective.

Sample

LOAD

LineID,

DocumentNo,

EndDate,

EndDate2,

date(if(len(trim([EndDate]))=0, "EndDate2", "EndDate")) +1 as StartDate

FROM [lib://app/forexperts.csv]

(txt, utf8, embedded labels, delimiter is ',', msq)

Above gives me the table like this:

LineIDDocumentNoStartDateEndDateEndDate2
1INVOICE10012018/1/12017/12/312017/12/31
2INVOICE10012018/1/12017/12/312017/12/31
3INVOICE10012018/1/12017/12/312017/12/31
4INVOICE10012018/1/12017/12/31
5INVOICE10012018/7/12018/6/30

What I am trying to do is to replace 2018/7/1 with 2018/1/1 if LineID=5 and DocumentNo=INVOICE1001.

Do you have any ideas?

Thanks

Tags (1)
1 Solution

Accepted Solutions
agigliotti
Honored Contributor II

Re: Replace specific cell IF

let's try the below script:

LOAD *,

     date( if(len(trim([EndDate]))=0, EndDate2, EndDate)) +1 ) as StartDate;

LOAD

LineID,

DocumentNo,

EndDate2,

if( LineID = 5 and DocumentNo='INVOICE1001', makeDate( 2017, 12, 31 ), EndDate ) as EndDate

FROM [lib://app/forexperts.csv]

(txt, utf8, embedded labels, delimiter is ',', msq)

6 Replies
agigliotti
Honored Contributor II

Re: Replace specific cell IF

could you better clarify your expected result for StartDate field ?

OR

for what i can understand you can try:

LOAD

LineID,

DocumentNo,

EndDate,

EndDate2,

if( LineID = 5 and DocumentNo='INVOICE1001', makeDate( 2018, 1, 1 ),

     date( if(len(trim([EndDate]))=0, EndDate2, EndDate)) +1 ) as StartDate

FROM [lib://app/forexperts.csv]

(txt, utf8, embedded labels, delimiter is ',', msq)

but i don't know if it make sense.

agf
New Contributor II

Re: Replace specific cell IF

StartDate is EndDate +1, unless EndDate is empty, then it is EndDate2+1

2018/1/1 = 2017/12/31 +1.

So I believe I would like to have EndDate set to whatever is in load file unless LineID=5 and DocumentNo=INVOICE1001. Then this field should be overwritten with the specific date. In this example 2017/12/31.

agigliotti
Honored Contributor II

Re: Replace specific cell IF

let's try the below script:

LOAD *,

     date( if(len(trim([EndDate]))=0, EndDate2, EndDate)) +1 ) as StartDate;

LOAD

LineID,

DocumentNo,

EndDate2,

if( LineID = 5 and DocumentNo='INVOICE1001', makeDate( 2017, 12, 31 ), EndDate ) as EndDate

FROM [lib://app/forexperts.csv]

(txt, utf8, embedded labels, delimiter is ',', msq)

agf
New Contributor II

Re: Replace specific cell IF

WORKED!

Can I ask 1 more question?

What would I need to do, to change more than 1 date?


So,if  LineID = 5 and DocumentNo=INVOICE1001 then StartDate should be 2018/1/1

and if LineID = 6 and DocumentNo=INVOICE1001 then StartDate should be 2018/8/1

and if LineID = 12 and DocumentNo=INVOICE1001 then StartDate should be 2018/3/1

andi f LineID = 12 and DocumentNo=INVOICE1009 then StartDate should be 2015/3/1

etc


I will have around 20 fields like that so some template would be great here!


Thank you @Andrea Gigliotti

agigliotti
Honored Contributor II

Re: Replace specific cell IF

I guess you have to manually write down your 20 if statement, but you can of course.

agigliotti
Honored Contributor II

Re: Replace specific cell IF

if you think your question has been answered please mark as Correct the answer and close this thread to help others.

Community Browser