Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mttconseil
New Contributor

Tag order as first order

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

Tags (1)
1 Solution

Accepted Solutions

Re: Tag order as first order

Sorry, left off the AS:

LEFT JOIN (Facts)

LOAD

CONTACT_EMAIL,

first_order_date as OrderDate,

1 as IsFirstOrder

RESIDENT CustomerData;

9 Replies
mttconseil
New Contributor

Re: Tag order as first order

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

Re: Tag order as first order

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

mttconseil
New Contributor

Re: Tag order as first order

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

Re: Tag order as first order

Sorry, left off the AS:

LEFT JOIN (Facts)

LOAD

CONTACT_EMAIL,

first_order_date as OrderDate,

1 as IsFirstOrder

RESIDENT CustomerData;

mttconseil
New Contributor

Re: Tag order as first order

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

Re: Tag order as first order

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

mttconseil
New Contributor

Re: Tag order as first order

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

Re: Tag order as first order

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

mttconseil
New Contributor

Re: Tag order as first order

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

Community Browser