topic Re: How to get the first order date and amount form the table? in App Development

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

gauravgg — Sun, 03 Apr 2016 06:40:08 GMT

I have the following order table

Customer_id order_id order_amount order_date

customer_id	order_id	order_amount	order_date
1	001	1000	01/01/2016
2	002	345	02/02/2016
3	003	324	03/03/2016
1	004	342	12/03/2016
4	005	234	20/03/2016
1	006	567	21/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_id	first_order_date	first_order_amount
1	01/01/2016	1000
2	02/02/2016	345
3	03/03/2016	324
4	20/03/2016	234

can anyone help me

thanks in advance

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

gauravgg — Sun, 03 Apr 2016 09:25:03 GMT

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

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

sunny_talwar — Sun, 03 Apr 2016 10:37:53 GMT

Try a straight table:

Dimension: customer_id

Expressions:

1) TimeStamp(Min(order_date))

2) FirstSortedValue(order_amount, order_date)

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

gauravgg — Mon, 04 Apr 2016 04:44:07 GMT

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

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

sunny_talwar — Mon, 04 Apr 2016 12:16:29 GMT

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;