topic Script / Loading first value in QlikView

Script / Loading first value

Wed, 25 Jan 2012 13:16:29 GMT

Hi,

I'm trying to load, for all of my customers, the first date they ordered.

I have 2 tables which are basically as following :

ORDERS :

LOAD

order_id,

customer_id,

order_date,

order_turnover

FROM QVD/orders.qvd

CUSTOMERS :

LOAD

customer_id,

customer_name,

customer_registration_date

FROM QVD/customers.qvd

I'd like to add a column in my "CUSTOMERS" table which would be the first order date (i want it to be pre-calculated in the script, not calculated within the report).

Thanks in advance for your help !

Damien

Re: Script / Loading first value

swuehl — Wed, 25 Jan 2012 13:22:51 GMT

Try a grouped by load with FirstSortedValue aggregation:

Add

left join (CUSTOMERS) LOAD

customer_id,

FirstSortedValue(order_date, order_date) as FirstOrderDate

resident ORDERS group by customer_id;

to your script, after the above posted lines.

I assume that order_date is of Date type with numerical representation.

Hope this helps,

Stefan

Re: Script / Loading first value

Wed, 25 Jan 2012 15:05:52 GMT

Thank you very much ! It works perfectly !