Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Employee
Employee

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

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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)

View solution in original post

6 Replies
Highlighted
Partner
Partner

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.

Highlighted
Employee
Employee

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.

Highlighted
Partner
Partner

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)

View solution in original post

Highlighted
Employee
Employee

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

Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

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