Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hy everybody,
I have a problem with the sales date.
I try to match the sales to the date they enter the system. However, it is not always filled and in that case I want to take the invoice date. This one is also not always filled and in the latter case I want to take order date. My question: How can i do that (in this order)?
Now I have read something about switch case but not enough to apply it. can anyone help me with explenation or an example?
Thank you in advance!!
My be try
Map_Order_date:
Load
Your ID,
Order Date
From Your Table
;
Sales Table
Load
Applymap('Map_Order_date',your ID,Null()) as OrderDate
If(Len(trim(SalesDate))>0,SalesDate,
If( (Len(Trim(InvoiceDate))>0 and InvoiceDate<>makedate(2017,1,1)),InvoiceDate,
Applymap('Map_Order_date',your ID,Null())
)) As NewDate
Have a look at the Alt() function
, maybe something like
LOAD
Date( Alt( SalesDate, InvoiceDate, OrderDate)) as NewDate,
...
FROM Facts;
Take care to handle your date fields appropriately:
Thankyou Stefan,
But it seems that I will get another problem. Invoice Date is sometimes (by default) filled with 01-01-2017 and I think that is not recognized by if (alt). if (alt) will choose the 01-01-2017 date I think. And what I want is that I can also filter this
Willem, I am not sure how you want to handle the Invoice Date's default value. Do you want to replace it when loading?
Or just handle it within the Alt() function?
LOAD
Date( Alt( SalesDate, If( InvoiceDate = Makedate(2017),Null(), InvoiceDate), OrderDate)) as NewDate,
...
FROM Facts;
Stefan,
It almost worked out but there's still one problem. the 'OrderDate' field is in another table. Do you also have a solution for that?
ps: Thanks in advance for your help!
My be try
Map_Order_date:
Load
Your ID,
Order Date
From Your Table
;
Sales Table
Load
Applymap('Map_Order_date',your ID,Null()) as OrderDate
If(Len(trim(SalesDate))>0,SalesDate,
If( (Len(Trim(InvoiceDate))>0 and InvoiceDate<>makedate(2017,1,1)),InvoiceDate,
Applymap('Map_Order_date',your ID,Null())
)) As NewDate
Thanks Sasidhar!
It worked out!
Great, Please close this thread by marking a correct answer and any helpful answers