Typical situation with records - Qlik Community

Anonymous · ‎2014-11-05

HI all,

I have a scenario where a hub gets activated and deactivated multiple times in a month. In this case, I need to get only latest activation date.

CUSTOMERID	key	UNITID	UNIT_STATUS	UNIT_TYPE	ACTIVATION_DATE	DEACTIVATION_DATE
1107021	5207	RADTEST15	ACTIVE	Devices	5/25/2014 0:00
1107021	5207	RADTEST15	ACTIVE	Devices	5/15/2014 0:00
1107021	5207	RADTEST15	ACTIVE	Devices	5/2/2014 0:00
1107021	5207	RADTEST15	DEACTIVE	Devices		5/20/2014 0:00
1107021	5207	RADTEST15	DEACTIVE	Devices		5/12/2014 0:00

Here I should get only the hub with activation_date of 5/25/2014. I know that its possible thro' max function.

Look at this scenario.

A hub gets activated and deactivated in the same month and acts again in the same month and deactivates in another month. And again activates in next month and deactivates later. In this case, I need all the records with activation and de-activaion dates side by side. And this has to be chronological.

A hub which got activated will be activated only on a later time but not before the activation date.

CUSTOMERID	key	UNITID	UNIT_STATUS	UNIT_TYPE	ACTIVATION_DATE	DEACTIVATION_DATE
1107022	5208	RADTEST17	ACTIVE	Devices	12/25/2014 0:00
1107022	5208	RADTEST17	ACTIVE	Devices	9/15/2014 0:00
1107022	5208	RADTEST17	ACTIVE	Devices	9/2/2014 0:00
1107022	5208	RADTEST17	DEACTIVE	Devices		10/20/2014 0:00
1107022	5208	RADTEST17	DEACTIVE	Devices		9/12/2014 0:00

My output should be

CUSTOMERID	key	UNITID	UNIT_STATUS	UNIT_TYPE	ACTIVATION_DATE	DEACTIVATION_DATE
1107022	5208	RADTEST17	ACTIVE	Devices	12/25/2014 0:00
1107022	5208	RADTEST17	ACTIVE	Devices	9/15/2014 0:00	10/20/2014 0:00
1107022	5208	RADTEST17	ACTIVE	Devices	9/2/2014 0:00	9/12/2014 0:00

Both the scenarios should be satisfied, because these are the records from the same table, and I need to create a monthly report.

And also, when month of september is selected, the second record in the output should have unit_status as 'ACTIVE', because it gets de-activated in the next month. and the third record in the output table should have the unit_status as 'DEACTIVE', because it de-activated in september and so on.

maxgro · ‎2014-11-05

first question

t1:

load * inline [

CUSTOMERID, key, UNITID, UNIT_STATUS, UNIT_TYPE ,ACTIVATION_DATE, DEACTIVATION_DATE

1107021, 5207, RADTEST15, ACTIVE, Devices, 5/25/2014 0:00

1107021, 5207, RADTEST15, ACTIVE, Devices, 5/15/2014 0:00

1107021, 5207, RADTEST15, ACTIVE, Devices, 5/2/2014 0:00

1107021, 5207, RADTEST15, DEACTIVE, Devices,, 5/20/2014 0:00

1107021, 5207, RADTEST15, DEACTIVE, Devices,, 5/12/2014 0:00

];

t1final:

NoConcatenate

load * resident t1

where Peek(CUSTOMERID) <> CUSTOMERID

order by CUSTOMERID, ACTIVATION_DATE desc;

DROP Table t1;

maxgro · ‎2014-11-05

2)

t2:

load * inline [

CUSTOMERID, key, UNITID, UNIT_STATUS, UNIT_TYPE, ACTIVATION_DATE, DEACTIVATION_DATE

1107022, 5208, RADTEST17, ACTIVE, Devices, 12/25/2014 0:00,

1107022, 5208, RADTEST17, ACTIVE, Devices, 9/15/2014 0:00,

1107022, 5208, RADTEST17, ACTIVE, Devices, 9/2/2014 0:00,

1107022, 5208, RADTEST17, DEACTIVE, Devices, , 10/20/2014 0:00

1107022, 5208, RADTEST17, DEACTIVE, Devices, , 9/12/2014 0:00

];

tmp:

load

*,

if(len(trim(ACTIVATION_DATE))>0, ACTIVATION_DATE, DEACTIVATION_DATE) as DATE

Resident t2

;

DROP Table t2;

tmp2:

NoConcatenate load

CUSTOMERID, key, UNITID, UNIT_STATUS, UNIT_TYPE,

ACTIVATION_DATE,

if(len(trim(DEACTIVATION_DATE))=0, Peek(DEACTIVATION_DATE), DEACTIVATION_DATE) as DEACTIVATION_DATE

Resident tmp

order by DATE desc;

DROP Table tmp;

Anonymous · ‎2014-11-06

Hi,

Thank you very much for the reply. I almost got it as I wanted. But, here, as I mentioned earlier, I need a solution that resolves both the scenarios combined.

This is the script that I modified basing on yours

t2:

load * inline [

CUSTOMERID, key, UNITID, UNIT_STATUS, UNIT_TYPE, ACTIVATION_DATE, DEACTIVATION_DATE

1107022, 5208, RADTEST17, ACTIVE, Devices, 12/25/2014 0:00,

1107022, 5208, RADTEST17, ACTIVE, Devices, 9/15/2014 0:00,

1107022, 5208, RADTEST17, ACTIVE, Devices, 9/2/2014 0:00,

1107022, 5208, RADTEST17, DEACTIVE, Devices, , 10/20/2014 0:00

1107022, 5208, RADTEST17, DEACTIVE, Devices, , 9/12/2014 0:00

1107021, 5207, RADTEST15, ACTIVE, Devices, 5/25/2014 0:00

1107021, 5207, RADTEST15, ACTIVE, Devices, 5/15/2014 0:00

1107021, 5207, RADTEST15, ACTIVE, Devices, 5/2/2014 0:00

1107021, 5207, RADTEST15, DEACTIVE, Devices,, 5/20/2014 0:00

1107021, 5207, RADTEST15, DEACTIVE, Devices,, 5/12/2014 0:00

];

tmp:

load

*,

if(len(trim(ACTIVATION_DATE))>0, ACTIVATION_DATE, DEACTIVATION_DATE) as DATE

Resident t2

;

DROP Table t2;

tmp2:

NoConcatenate load

CUSTOMERID, key, UNITID, UNIT_STATUS, UNIT_TYPE,

ACTIVATION_DATE,

if(len(trim(DEACTIVATION_DATE))=0 and Peek(key)=key, Peek(DEACTIVATION_DATE), DEACTIVATION_DATE) as DEACTIVATION_DATE

Resident tmp

order by DATE desc;

t1:

Load 1 as Rowkey,*,MonthName(Timestamp#(ACTIVATION_DATE,'MM/DD/YYYY hh:mm')) AS ACTIVATION_DATE_MONTH

Resident tmp2

Where len(ACTIVATION_DATE)>0;

DROP Tables tmp,tmp2;

I got this table from the logic that you have applied in the script earlier.

key	UNITID	CUSTOMERID	UNIT_STATUS	UNIT_TYPE	ACTIVATION_DATE	DEACTIVATION_DATE
5207	RADTEST15	1107021	ACTIVE	Devices	5/2/2014 0:00	5/12/2014 0:00
5207	RADTEST15	1107021	ACTIVE	Devices	5/15/2014 0:00	5/20/2014 0:00
5207	RADTEST15	1107021	ACTIVE	Devices	5/25/2014 0:00
5208	RADTEST17	1107022	ACTIVE	Devices	9/2/2014 0:00	9/12/2014 0:00
5208	RADTEST17	1107022	ACTIVE	Devices	9/15/2014 0:00	10/20/2014 0:00
5208	RADTEST17	1107022	ACTIVE	Devices	12/25/2014 0:00

If a user selects month 'MAY', then he should see only one record i.e the latest active record from that month. This is because, if a hub gets activated and deactivated multiple times in a single month then only one record(which is the latest) has to be displayed.

Where as, if a hub gets activated and deactivated across multiple months just as the hub with key 5208, then all the records should be displayed.

Month filter is extracted from activation_date field.

Thanks in advance.