topic Re: Start and End Date from Multiple Rows in QlikView

Start and End Date from Multiple Rows

sjhussain — Sat, 05 Mar 2016 20:37:25 GMT

Dear All,

Hope everyone is fine.

We have a HLR Table consisting of IMEI_NO, IMSI_NO, DATE. What we want to do is for each combination of IMEI_NO and IMSI_NO have a start and end date.

The start date would be the first occurance of the IMEI + IMSI comination and then the end date would be today's date.

For example:

IMEI_NO, IMSI_NO, DATE

IMEI_1, IMSI_1, Jan 12, 2016

IMEI_1, IMSI_1, Jan 16, 2016

IMEI_1, IMSI_1, Feb 13, 2016

IMEI_2, IMSI_2, Jan 15, 2016

IMEI_2, IMSI_3, Jan 31, 2016

IMEI_2, IMSI_2, Feb 18, 2016

Resultant should be

IMEI_NO, IMSI_NO, START_DATE, END_DATE

IMEI_1, IMSI_1, JAN 12, 2016, MAR 5, 2016

IMEI_2, IMSI_2, Jan 15, 2016, MAR 5, 2016

IMEI_2, IMSI_3, Jan 31, 2016, MAR 5, 2016

Will appreciate if someone can assist us.

Thanks.

Re: Start and End Date from Multiple Rows

swuehl — Sat, 05 Mar 2016 20:51:36 GMT

Make sure your dates are read in correctly as dual values:

Get the Dates Right

Why don’t my dates work?

Then something like this should work

LOAD IMEI_NO,

IMSI_NO,

Date(Min(DATE)) as START_DATE,

Today(1) as END_DATE

RESIDENT HLR

GROUP BY IMEI_NO, IMSI_NO;

DROP TABLE HLR;

Re: Start and End Date from Multiple Rows

Digvijay_Singh — Sat, 05 Mar 2016 21:10:02 GMT

Like this -

T1:

Load IMEI_NO, IMSI_NO,Date(Date#(DATE,'MMM DD, YYYY'),'MMM DD, YYYY') as DATE;

Load * inline [

IMEI_NO, IMSI_NO, DATE

IMEI_1, IMSI_1, "Jan 12, 2016"

IMEI_1, IMSI_1, "Jan 16, 2016"

IMEI_1, IMSI_1, "Feb 13, 2016"

IMEI_2, IMSI_2, "Jan 15, 2016"

IMEI_2, IMSI_3, "Jan 31, 2016"

IMEI_2, IMSI_2, "Feb 18, 2016" ];

F1:

Load

IMEI_NO, IMSI_NO,

Min(Date(Date#(DATE,'MMM DD, YYYY'),'MMM DD, YYYY')) as START_DATE,

DATE(Today(),'MMM DD, YYYY') as END_DATE

Resident T1

Group By IMEI_NO, IMSI_NO;

Drop table T1;