Replace specific cell IF

[Krzysztof-Wroclawski]

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:

LineID	DocumentNo	StartDate	EndDate	EndDate2
1	INVOICE1001	2018/1/1	2017/12/31	2017/12/31
2	INVOICE1001	2018/1/1	2017/12/31	2017/12/31
3	INVOICE1001	2018/1/1	2017/12/31	2017/12/31
4	INVOICE1001	2018/1/1		2017/12/31
5	INVOICE1001	2018/7/1	2018/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

[agigliotti]

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.

[Krzysztof-Wroclawski]

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 · Accepted solution]

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)

[Krzysztof-Wroclawski]

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]

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

[agigliotti]

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