topic Re: Sort by two dimensions in QlikView

Sort by two dimensions

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello, I have a script with clients and dates and I would like to get a table sort by clients and dates. Then use the above function to calculate a delay between each customer's date. Is this possible? Thank you for your help

Data :

load* Inline

[Customer, date, order, amount

4189,17/03/2015,45988038,73

6745,22/10/2017,46351840,66

5607,12/08/2016,46237091,83

1000000963,02/06/2016,32897484,105

1000000369,22/12/2017,46296443,97

6745,05/03/2017,32503124,116

1000000963,02/06/2016,32897484,105

400483848,23/05/2017,46102582,33

1000000369,03/07/2017,32602883,99

1000000369,08/05/2016,32721423,102

1000000593,18/05/2017,32342821,26

1000000369,06/07/2015,32909680,149

5607,13/08/2017,46238408,61

4189,22/10/2017,46351840,66

1000000593,19/05/2017,32342821,26

1000000963,02/06/2016,32897484,105

4189,22/10/2017,46351840,66

1000001048,25/08/2017,32694333,160

];

Re: Sort by two dimensions

Anonymous — Wed, 22 Nov 2017 08:08:03 GMT

Just add this below:

Data_sorted:

NoConcatenate LOAD * Resident Data Order by Customer,date;

Then you would be able to calculate the margins between the dates.

Re: Sort by two dimensions

sumanta12 — Wed, 22 Nov 2017 08:22:23 GMT

Hi,

Try this:

Data :

load * Inline [

Customer,date,order,amount

4189,17/03/2015,45988038,73

6745,22/10/2017,46351840,66

5607,12/08/2016,46237091,83

1000000963,02/06/2016,32897484,105

1000000369,22/12/2017,46296443,97

6745,05/03/2017,32503124,116

1000000963,02/06/2016,32897484,105

400483848,23/05/2017,46102582,33

1000000369,03/07/2017,32602883,99

1000000369,08/05/2016,32721423,102

1000000593,18/05/2017,32342821,26

1000000369,06/07/2015,32909680,149

5607,13/08/2017,46238408,61

4189,22/10/2017,46351840,66

1000000593,19/05/2017,32342821,26

1000000963,02/06/2016,32897484,105

4189,22/10/2017,46351840,66

1000001048,25/08/2017,32694333,160

];

DATA1:

LOAD

date-PREVIOUS(date) AS INTERVAL

RESIDENT Data ORDER BY Customer,date;

DROP TABLE Data;

Re: Sort by two dimensions

Anonymous — Wed, 22 Nov 2017 08:38:32 GMT

This would calculate the interval regardless of the customer, so in addition I would suggest the following:

DATA1:

NoConcatenate LOAD *,

if(customer=Previous(Customer),date-Previous(date),0) as INTERVAL

Resident Data ORDER By Customer, date;

Drop Table Data;

Re: Sort by two dimensions

antoniotiman — Wed, 22 Nov 2017 08:54:38 GMT

May be thsi

If(Customer=Above(TOTAL Customer),Interval(date-Above(TOTAL date),'DD'),0)

Re: Sort by two dimensions

Anonymous — Wed, 22 Nov 2017 09:01:22 GMT

thank you

it's almost that but can you tell me how to limit this interval calculation for each customer?

Re: Sort by two dimensions

Anonymous — Wed, 22 Nov 2017 09:10:12 GMT

thank you but I would like to keep this calculation in the script

Re: Sort by two dimensions

sumanta12 — Wed, 22 Nov 2017 09:13:01 GMT

Hi Saoud,

If the interval is negative then it will show 0. Is it or something else?

Re: Sort by two dimensions

antoniotiman — Wed, 22 Nov 2017 09:16:50 GMT

Data:

Load * From ...;

NoConcatenate
LOAD *,If(Customer=Peek(Customer),Interval(date-Peek(date),'DD'),0) as Diff
Resident Data
Order By Customer,date;
Drop Table Data;

Re: Sort by two dimensions

Anonymous — Wed, 22 Nov 2017 09:17:27 GMT

no because it is possible that the delay between two different customers is positive

thanks

Re: Sort by two dimensions

sumanta12 — Wed, 22 Nov 2017 09:22:06 GMT

Hi Saoud,

Sorry....

Not understood your requirement. Can you please explain again.

Re: Sort by two dimensions

sumanta12 — Wed, 22 Nov 2017 09:28:32 GMT

Hi,

Please try this:

Data :

load * Inline [

Customer,date,order,amount

4189,17/03/2015,45988038,73

6745,22/10/2017,46351840,66

5607,12/08/2016,46237091,83

1000000963,02/06/2016,32897484,105

1000000369,22/12/2017,46296443,97

6745,05/03/2017,32503124,116

1000000963,02/06/2016,32897484,105

400483848,23/05/2017,46102582,33

1000000369,03/07/2017,32602883,99

1000000369,08/05/2016,32721423,102

1000000593,18/05/2017,32342821,26

1000000369,06/07/2015,32909680,149

5607,13/08/2017,46238408,61

4189,22/10/2017,46351840,66

1000000593,19/05/2017,32342821,26

1000000963,02/06/2016,32897484,105

4189,22/10/2017,46351840,66

1000001048,25/08/2017,32694333,160

];

DATA1:

LOAD

FABS(date-PREVIOUS(date)) AS INTERVAL

RESIDENT Data ORDER BY Customer,date;

DROP TABLE Data;

Re: Sort by two dimensions

Anonymous — Wed, 22 Nov 2017 09:51:08 GMT

thanks a lot

Re: Sort by two dimensions

Anonymous — Wed, 22 Nov 2017 09:53:26 GMT

not exactly because the delay between two different customers must be zero

I have a good answer by antoniotiman

If(Customer=Peek(Customer),Interval(date-Peek(date),'DD'),0) as Diff

thanks for your help