Tag order as first order

[Anonymous]

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

[Anonymous]

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

[rwunderlich]

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

[Anonymous]

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

[rwunderlich · Accepted solution]

Sorry, left off the AS:

LEFT JOIN (Facts)

LOAD

CONTACT_EMAIL,

first_order_date as OrderDate,

1 as IsFirstOrder

RESIDENT CustomerData;

[Anonymous]

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

[rwunderlich]

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

[Anonymous]

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

[rwunderlich]

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

[Anonymous]

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