Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Thank you so much. That did it!
Ack! Realized it needs to be a CrossTable. Apologies, but any advice on the same dataset but as a crosstable?
Thanks!
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?
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.
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;