How to split the date range in chart as time dimention

[#1]

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

[jagan]

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.

[#3]

Thanks Mohan.

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

[tresesco]

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

[tresesco]

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

[#6]

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

[aveeeeeee7en · Accepted solution]

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.

[jagan]

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.

[tresesco]

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.

[aveeeeeee7en]

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