Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

gauravgg
New Contributor III

How to get the first order date and amount form the table?

hi

I have the following  order table

Customer_id order_id order_amount order_date

customer_idorder_idorder_amountorder_date
1001100001/01/2016
200234502/02/2016
300332403/03/2016
100434212/03/2016
400523420/03/2016
100656721/03/2016

form the above table i want to make another table using resident load in which in want the following

i want the customer_id and it's first order date and first order amount as follows

customer_idfirst_order_datefirst_order_amount
101/01/20161000
202/02/2016345
303/03/2016324
420/03/2016234

can anyone help me

thanks in advance

4 Replies
gauravgg
New Contributor III

Re: How to get the first order date and amount form the table?

one important thing i forgot to add

the order_date field is a timestamp field

and the customer can have multipal order on the single day

so also based on time i want the first order date and order amount

for example

a customer_id have order on the some day like

on 01/01/2016  but order 4 times on different time .

so from that i want that order value that he ordered for the first time on that date

MVP
MVP

Re: How to get the first order date and amount form the table?

Try a straight table:

Dimension: customer_id

Expressions:

1) TimeStamp(Min(order_date))

2) FirstSortedValue(order_amount, order_date)

gauravgg
New Contributor III

Re: How to get the first order date and amount form the table?

hi

sunny

i want to create a table in the data model

cusromer_id ,first_order_date,first_order_value

how to create table in data model using resident load

thanks in advance

MVP
MVP

Re: How to get the first order date and amount form the table?

Try this following script:

Table:

LOAD customer_id,

    order_id,

    order_amount,

    order_date

FROM

[https://community.qlik.com/thread/211819]

(html, codepage is 1252, embedded labels, table is @1)

Where Len(Trim(customer_id)) > 0;

GroupedTable:

LOAD customer_id,

  Date(Min(order_date)) as first_order_date,

  FirstSortedValue(order_amount, order_date) as first_order_amount

Resident Table

Group By customer_id;