topic Re: How to split the date range in chart as time dimention in QlikView

How to split the date range in chart as time dimention

Tue, 22 Jul 2014 05:16:19 GMT

Hello there!

I have the following data, I want to dispaly these information on a daily basis in dimension.

I allready have tried this in load statement but due to huge number of records this is not working.

I will be greatful if anyone help to get the following results:

Data:

Min Date Max Date Quantity

24/06/2014 28/06/2014 17

29/06/2014 05/07/2014 20

06/07/2014 09/07/2014 12

Results :

Date Quantity

24/06/2014 17

25/06/2014 17

26/06/2014 17

27/06/2014 17

28/06/2014 17

29/06/2014 20

30/06/2014 20

01/07/2014 20

02/07/2014 20

03/07/2014 20

04/07/2014 20

05/07/2014 20

06/07/2014 12

07/07/2014 12

08/07/2014 12

09/07/2014 12

Re: How to split the date range in chart as time dimention

jagan — Tue, 22 Jul 2014 05:38:44 GMT

Hi,

Try this script

Data:

LOAD

Date(Min_Date + IterNo() - 1) AS Date,

Quantity

WHILE Floor(Min_Date + IterNo() - 1) <= Max_Date;

LOAD

Date(Date#(Min_Date, 'DD/MM/YYYY')) AS Min_Date,

Date(Date#(Max_Date, 'DD/MM/YYYY')) AS Max_Date,

Quantity

INLINE [

Min_Date, Max_Date, Quantity

24/06/2014, 28/06/2014, 17

29/06/2014, 05/07/2014, 20

06/07/2014, 09/07/2014, 12];

Regards,

jagan.

Re: How to split the date range in chart as time dimention

Tue, 22 Jul 2014 05:42:11 GMT

Thanks Mohan.

As I mentioned earlier data is too big - these are only sample data.
I want to do this in Chart.

Re: How to split the date range in chart as time dimention

tresB — Tue, 22 Jul 2014 05:42:44 GMT

load
Date([Min Date]+Iterno()-1) as [Date],
Quantity,
While [Min Date]+Iterno()-1<=[Max Date];

Load * Inline [
"Min Date",          "Max Date",      Quantity
24/06/2014,      28/06/2014,      17
29/06/2014,      05/07/2014,      20
06/07/2014,      09/07/2014,      12
];

PFA

Re: How to split the date range in chart as time dimention

tresB — Tue, 22 Jul 2014 05:49:41 GMT

Doing in the chart would be very inefficient way. Better you do it in the script and try to optimize it there.

Re: How to split the date range in chart as time dimention

Tue, 22 Jul 2014 05:50:14 GMT

When I tried to load this takes many hours as our data is huge.

Data size for your reference :

6 millions (Stock line items) X 365 days (Date)= +2 billions

Re: How to split the date range in chart as time dimention

aveeeeeee7en — Tue, 22 Jul 2014 06:11:10 GMT

Hi Mehboob

Use this Code in your script:

Load *,
Date(Floor([Min Date])+IterNo()-1,'DD/MM/YYYY') as [Min Date1],
If(Floor([Min Date])+IterNo()-1<[Max Date], Date(Floor([Min Date])+IterNo()-1,'DD/MM/YYYY'), [Max Date]) as [Max Date1]
FROM Data

While Date(Floor([Min Date]),'DD/MM/YYYY')+IterNo()-2<[Max Date];

Kindly change the field names with your Application Field Names and also change the Table name and its source.

No matter how many records are there in your Data, it will work.

See the Attachment.

Re: How to split the date range in chart as time dimention

jagan — Tue, 22 Jul 2014 06:12:24 GMT

Hi,

When you are loading this many records it will take time, even if you query in database it needs sometime to pull right, the same to process this many records it will take time. Do this in script is efficient way, instead of doing it in front end.

Regards,

Jagan.

Re: How to split the date range in chart as time dimention

tresB — Tue, 22 Jul 2014 06:20:35 GMT

I guess you have to analyze your data in a better way. If you say :

"6 millions (Stock line items) X 365 days (Date)= +2 billions"

I tend to think its a wrong analysis. This suggests you have 6 million unique stock items and you have only one transaction for every year and you are trying to generate daily based records from yearly frequency, which is not an usual case. Otherwise, a better data modelling could help too.

Re: How to split the date range in chart as time dimention

aveeeeeee7en — Tue, 22 Jul 2014 10:00:43 GMT

I recommend you to use Tresesco Sir Method:

Load *

Date([Min Date]+Iterno()-1) as [Date],
Quantity
Resident BBB
While [Min Date]+Iterno()-1<=[Max Date];

Regards

Aviral Nag