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

[#1]

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

[swuehl]

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;

[#3]

Thank you so much.  That did it!

[#4]

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

Thanks!

[swuehl]

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?

[#6]

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 · Accepted solution]

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;