Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Customers with "3rd order..."

Hello community.

I have interesting task, in my head I have idea how to solve this, but I not sure that I good enough to put this idea to "script code"...

So please suggest something...

I need to create a table which have inside customers who have 3rd order in specific month (accordingly to order date)  of specific customer.

I have raw source data like:

Order number
Order date (DDMMYYYY)Customer ID
abc123412042016CustA
sdf213510022015CustB
asw345520012013CustA
ert543213042016CustC
dfg567817052016CustD
asd345614052016CustC
ghj456717072016CustC
sdf456730012015CustA
ewr432505072016CustA
sdf434523052015CustB
etc.......

etc....

etc....

Close to 1 million lines.

As result  I need to have aggregated table, where only customers who have 3 and more orders included and where is date of 3rd order specified:

CustomerID3rd Order Date
CustA12042016
CustC17072016
etc......

CustB have only 2 orders, CustC have only 1 order so they out of selection...

I prefer to do this table during script load, because after I need to do some extra actions with this resulting table...

As I understand I should sort source table by customer name then sort orders by order date inside every specific customer, then apply autonumber for orders (but autonumber must be applied for orders inside every specific customer, and for every next customer autonumber of orders must start from 1 again). And finally I will simply select all orders with AutoNumberField = 3.

But I almost have no experience with Autonumber function... Please suggest how to realize such transformation...

Maybe other ideas for transformation logic...

Thank you in advance!

1 Solution

Accepted Solutions
jpenuliar
Not applicable

Re: Customers with "3rd order..."

Hi Maxim,

Maybe below script using FirstSortedValue function is you need:

Temp:

LOAD RecNo(),

     [Order number],

     [Order date],

     [Customer ID]

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

FirstSortedValue:

LOAD

    [Customer ID],

    FirstSortedValue([Order number], [Order date],3) as [3rd ORDER NUMBER],

    FirstSortedValue([Order date], [Order date],3) as [3rd ORDER DATE]    

Resident Temp Group By [Customer ID];

5 Replies
jpenuliar
Not applicable

Re: Customers with "3rd order..."

Hi Maxim,

Maybe below script using FirstSortedValue function is you need:

Temp:

LOAD RecNo(),

     [Order number],

     [Order date],

     [Customer ID]

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

FirstSortedValue:

LOAD

    [Customer ID],

    FirstSortedValue([Order number], [Order date],3) as [3rd ORDER NUMBER],

    FirstSortedValue([Order date], [Order date],3) as [3rd ORDER DATE]    

Resident Temp Group By [Customer ID];

jpenuliar
Not applicable

Re: Customers with "3rd order..."

here's how it looks like on tables

Not applicable

Re: Customers with "3rd order..."

Hello Jonathan,

looks great! I just never used before the function.

will study it.

Thnak you very much!

vinieme12
Not applicable

Re: Customers with "3rd order..."

That or you could extend the flexibility within the same table with a flag to show ordernumber

, this way you could do 3rd,4th or whatever number you feel

ordernumber.JPG

TEMPTABLE:

LOAD Customer,

     Dateval,

     Month(Dateval) as TranMonth,

  1 as tempval

FROM

(ooxml, embedded labels, table is Sheet1);

NOCONCATENATE

Table1:

LOAD

  Customer,

  Dateval,

  TranMonth,

    if( Customer = Previous(Customer) and TranMonth = Previous(TranMonth),

  rangesum(tempval,peek(OrderNum)),tempval) as OrderNum

RESIDENT TEMPTABLE

Where len(Customer)>0

ORDER BY Customer,Dateval ASC;

Drop Table TEMPTABLE;

Not applicable

Re: Customers with "3rd order..."

Hello Vineeth,

also nice solution.

Thank you!