topic Crosstable issue in QlikView

Crosstable issue

Anonymous — Thu, 16 Apr 2015 17:28:57 GMT

Hi ,

I have an issue, where i have date field with sales data for the day which is getting added daily in to the excel sheet. when i perform cross table load the the new date is not being reloaded. Again I have reload this task manually. please advice

Regards,

John

Re: Crosstable issue

petter — Thu, 16 Apr 2015 17:52:41 GMT

Is the number of columns increasing for each day? Do you have a sample spreadsheet to share?

Re: Crosstable issue

petter — Thu, 16 Apr 2015 17:59:44 GMT

If the number of rows is constant and the columns are dynamic you might be able to take advantage of the TRANSFORM / ROTATE / TRANSPOSE. But it is much easier to give advice if you have a sample of your source Excel. Or even just a description of the number of "dimension" fields.

Re: Crosstable issue

sunny_talwar — Thu, 16 Apr 2015 18:04:03 GMT

I ran into a similar problem once and what I did was to bring in extra columns for 5 years worth of additions, in your case this may or may not work but you can give it a try.

Sunny

Re: Crosstable issue

Anonymous — Thu, 16 Apr 2015 18:06:20 GMT

Hi Petter,

please find attached sheet.

Regards

John

Re: Crosstable issue

Anonymous — Thu, 16 Apr 2015 18:10:19 GMT

Hi Petter,

Yes only the columns getting added no extra rows nothing

Regards

John

Re: Crosstable issue

sunny_talwar — Thu, 16 Apr 2015 18:15:42 GMT

Modified your excel file. Please find attached the app and excel file.

HTH

Best,

Sunny

Re: Crosstable issue

Anonymous — Thu, 16 Apr 2015 18:21:05 GMT

Sorry Sunny this is not what I'm looking for .....

Re: Crosstable issue

sunny_talwar — Thu, 16 Apr 2015 18:22:08 GMT

Not a problem

I hope you can find your solution.

Best,

Sunny

Re: Crosstable issue

petter — Thu, 16 Apr 2015 18:41:26 GMT

This Load Script code should do the trick for you:

LOAD Product as Day,
    P1,
    P2,
    P3,
    P4,
    P5,
    P6,
    P7
FROM
[..\Downloads\Sample (1).xlsx]
(ooxml, embedded labels, table is Sheet2, filters(
Transpose()
));

Re: Crosstable issue

petter — Thu, 16 Apr 2015 18:45:17 GMT

Actually you need to wrap Product in a Date() function like this

Date(Product) AS Day

It may seem strange that Product suddenly becomes Day - but that is because the label Product is in the

pivot point of the transpose so it becomes the header for the Days after the transpose...

Re: Crosstable issue

Anonymous — Fri, 17 Apr 2015 05:14:21 GMT

Thanks Petter, this is something really interesting which I was not aware of...will try this and come up with more ..

So how does this get done if have

Category Product 16/04/2015 17/04/2015

Child P1, 5 10

P2 10 15

Men's Wear P3, 15 20

P4 20 30

Re: Crosstable issue

petter — Fri, 17 Apr 2015 05:56:04 GMT

Then I would rather use CROSSTABLE (....) LOAD instead and a PIVOT TABLE like this:

Re: Crosstable issue

buzzy996 — Fri, 17 Apr 2015 10:32:48 GMT

PFA, hopes simple cross table will helps here,