Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am using QlikSense.
I have an excel spreadsheet with all the orders lines (so 1 order can spread on multiple lines).
OrderID,
Contact_Email,
Line_amount,
OrderDate
My goal is to be able to tag the first orders for each client ==> From my table I can now get the customer data with the first order date and the last order date. Therefore if the order_date = customer_first_order_date then I can flag the order as a "first order".
Could you help me doing that?
--------------------------------------------
As of now, here is the script I have :
Facts:
LOAD
OrderID,
DATE(FLOOR(FIRST_INVOICE_DATE)) as OrderDate,
If(LINE_TYPE='Product',QUANTITY,0) as ProductRecordCount,
CONTACT_EMAIL,
Line_amount
FROM [lib://DataFiles/InvoiceDetail.xlsx]
(ooxml, embedded labels, table is [InvoiceDetail]);
OrderData:
LOAD
OrderID,
date(min(OrderDate)) as invoice_date,
1 AS OrderRecordCount,
sum(Line_amount) as CA_HT_NET,
count(OrderID) as OrderLinesCount
RESIDENT Facts
group by OrderID;
CustomerData:
LOAD
CONTACT_EMAIL,
date(min(OrderDate)) as first_order_date,
date(max(OrderDate)) as last_order_date,
count(distinct(OrderID)) as nborders
RESIDENT Facts
group by CONTACT_EMAIL;
==> At this step, I would like to add the field IsFirstOrder in the OrderData table (but may be this is not the right way to do that, feel free to propose me your solution ;-))
Thank you in advance for your help.
Warm Regards,
MT
Sorry, left off the AS:
LEFT JOIN (Facts)
LOAD
CONTACT_EMAIL,
first_order_date as OrderDate,
1 as IsFirstOrder
RESIDENT CustomerData;
Hello,
Anybody?
May be I was not clear enough... In fact, I just would like to be able to tag first order at load time if possible. For that, my starting point is the list of all the orders lines (so 1 order can spread on multiple lines).
Hope you can help,
Warm Regards,
MT
You can tag all orders from the first date for each customer by adding this to your script.
LEFT JOIN (Facts)
LOAD
CONTACT_EMAIL,
first_order_date,
1 as IsFirstOrder
RESIDENT CustomerData;
You can also do something similar with the FirstSortedValue() function. See the Help for info on that Function.
-Rob
Hello Rob,
Thank you for your answer.
However, when I paste your code just after mine, a synthetic key is created and all the order lines will have IsFirstOrder=1.
I have also tried :
LEFT JOIN (Facts)
LOAD
CONTACT_EMAIL,
if (first_order_date=dateId,1,0) as IsFirstOrder
RESIDENT CustomerData;
But in that case, I have an error message saying that the field dateId was not found.
Could you help?
Thank you in advance,
MT
Sorry, left off the AS:
LEFT JOIN (Facts)
LOAD
CONTACT_EMAIL,
first_order_date as OrderDate,
1 as IsFirstOrder
RESIDENT CustomerData;
Hello Rob,
This time, there is no more synthetic key, and the OrderDate field has been added to the Facts table.
However, all the lines will have IsFirstOrder = 1.
Any idea?
I think that I should add something like
if (first_order_date=OderDate,1,0) as IsFirstOrder
But no way to make it work...
Thank you in advance,
MT
I was counting on the fact that first_order_date was created as max(OrderDate), so it seems it should work. Do you have a range of dates for each CONTACT_EMAIL. Can you post a sample QVW or sample data?
-Rob
Hello,
Attached are an excel spreadsheet with some examples :
Example : first order for f.andrieux is 08/07/2011. Therefore, the order CMNF1100000704 is tagged FirstOrder=1 while the orders CMNF1100000750 & CMNF1100000853 (from the same customer) are tagged FirstOrder=0.
Hope you can help with that 😉
Warm Regards,
MT
It works me with the sample data you provided, See attached QVW. If it's not working for you, please attach a sample QVW.
-Rob
Hello Rob,
Indeed you are right! It is working fine now !
Thank you so much for the time you took helping me.
Warm Regards,
MT