Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data and Dates in Weeks need to (in QV) breakout into by Day

Hey everyone-

Wondering if someone could help me out here.  I have a set of data that is aggregated by week and need to break this out into by day.  Unfortunately, I don't have access to the primary data source, so I have to do this into qlikview.  Was wondering if someone could help out and give advice?

See attached QVW.

Thank!s

1 Solution

Accepted Solutions
swuehl
MVP
MVP

That should not be much different, just use a CROSSTABLE LOAD:

Set DateFormat = 'M/D/YYYY';

INPUT:

CROSSTABLE (Item,Cost) LOAD * INLINE

[WeekDate,Clothes,Hats

1/3/2011, 784400, 142800

1/10/2011, 951400, 249732

1/17/2011, 945100, 93100

1/24/2011, 1213145, 334532

1/31/2011, 755552, 33556

2/7/2011, 755552, 33556

2/14/2011, 155555, 294264

];

RESULT:

LOAD

Item, Date(WeekDate+iterno()-1) as Date, Cost/7 as Cost

Resident INPUT while IterNo() <=7;

drop table INPUT;

View solution in original post

6 Replies
swuehl
MVP
MVP

If you want to distribute your values equal to the dates, you could do it similar to:

INPUT:

LOAD * INLINE

[WeekDate,Clothes,Hats

1/3/2011, 784400, 142800

1/10/2011, 951400, 249732

1/17/2011, 945100, 93100

1/24/2011, 1213145, 334532

1/31/2011, 755552, 33556

2/7/2011, 755552, 33556

2/14/2011, 155555, 294264

];

RESULT:

LOAD

Date(WeekDate+iterno()-1) as Date, Clothes/7 as Clothes, Hats/7 as Hats

Resident INPUT while IterNo() <=7;

drop table INPUT;


Not applicable
Author

Thank you so much.  That did it!

Not applicable
Author

Ack! Realized it needs to be a CrossTable.  Apologies, but any advice on the same dataset but as a crosstable?

Thanks!

swuehl
MVP
MVP

A crosstable in the frontend? Just use a pivot table for that. Or maybe I'm misunderstanding what you want, could you post an example of the table layout?

Not applicable
Author

swuehl-

Thanks for the quick response. What I meant is that same sample data that I provided needs to be transformed as a cross table before it gets into QV.  Basically "Clothes" and "Hats" need to be in one field, and the numerical values in another field.  So it would look like this:

WeekDate, Item, Cost

1/3/2011, Hat, 12384.22

1/3/2011, Clothes, 4848038.10

Hope that makes sense.  Thanks again for your help.

swuehl
MVP
MVP

That should not be much different, just use a CROSSTABLE LOAD:

Set DateFormat = 'M/D/YYYY';

INPUT:

CROSSTABLE (Item,Cost) LOAD * INLINE

[WeekDate,Clothes,Hats

1/3/2011, 784400, 142800

1/10/2011, 951400, 249732

1/17/2011, 945100, 93100

1/24/2011, 1213145, 334532

1/31/2011, 755552, 33556

2/7/2011, 755552, 33556

2/14/2011, 155555, 294264

];

RESULT:

LOAD

Item, Date(WeekDate+iterno()-1) as Date, Cost/7 as Cost

Resident INPUT while IterNo() <=7;

drop table INPUT;