topic Re: How to load multi dimension cross table into QV? in QlikView

How to load multi dimension cross table into QV?

wendytham — Wed, 25 Nov 2020 16:16:04 GMT

Hi. How to load this data into QV? Please assist. Thank you.

Re: How to load this data into QV?

sunny_talwar — Wed, 29 Nov 2017 17:19:39 GMT

May be this

The Crosstable Load

Re: How to load this data into QV?

devarasu07 — Wed, 29 Nov 2017 17:32:10 GMT

HI,

you can try with Cross table load or generic load method,

example,

cross table,

CrossTable(Month, Amount, 3)
LOAD Category,
    Model,
    [Month Year],
    [Apr-17],
    [May-17],
    [Jun-17],
    [Jul-17],
    [Aug-17],
    [Sep-17],
    [Oct-17],
    [Nov-17],
    [Dec-17],
    [Jan-18],
    [Feb-18],
    [Mar-18]
FROM

(ooxml, embedded labels, table is Sheet2);

Thanks,

Deva

Re: How to load this data into QV?

chriscammers — Wed, 29 Nov 2017 19:08:20 GMT

Actually you're going to need to do a crosstable and generic load

This will give you a data model that looks like this...

Re: How to load this data into QV?

wendytham — Thu, 30 Nov 2017 00:02:12 GMT

I need the data to be loaded as below format. Can anyone assist on this? Thank you.

Re: How to load this data into QV?

sunny_talwar — Thu, 30 Nov 2017 01:23:21 GMT

Did you look at all the suggestions? I think that is what CrossTable prefix ought to do

Re: How to load this data into QV?

wendytham — Thu, 30 Nov 2017 05:44:29 GMT

Hi Sunny, sorry, I am new to QV. I have looked through all the suggestions, but I still can't load the format I want.

The 1st and 2nd column is generic table and "column 3, row 1 to 11" is cross table. How to combine both in one table?

Thank you.

Re: How to load multi dimension cross table into QV?

sunny_talwar — Thu, 30 Nov 2017 17:34:52 GMT

Try this

Table:

CrossTable([Month Year], Data, 3)

LOAD Category,

Model,

[Month Year] as Labels,

[Apr-17],

[May-17],

[Jun-17],

[Jul-17],

[Aug-17],

[Sep-17],

[Oct-17],

[Nov-17],

[Dec-17],

[Jan-18],

[Feb-18],

[Mar-18]

FROM

[..\..\..\Downloads\sample data (3).xlsx]

(ooxml, embedded labels, table is Sheet2);

FinalTable:

LOAD Distinct

Category,

Model,

[Month Year]

Resident Table;

FOR i = 1 to FieldValueCount('Labels')

LET vField = FieldValue('Labels', $(i));

If Len(Trim('$(vField)')) > 0 THEN

Left Join (FinalTable)

LOAD Category,

Model,

[Month Year],

Data as [$(vField)]

Resident Table

Where Labels = '$(vField)';

ENDIF

NEXT

DROP Table Table;

Re: How to load multi dimension cross table into QV?

wendytham — Mon, 04 Dec 2017 00:56:58 GMT

It works! Thank you so much Sunny!

Re: How to load multi dimension cross table into QV?

wendytham — Wed, 06 Dec 2017 00:12:40 GMT

Hi Sunny,

Could you help me on this?

I would like to know how to create a new variable in the script for the below formula.

Market ratio:

(Number of jobs of current month x 12 months) / (total sales unit of last 12 months).

I would like to exclude the job with part#=no_parts. I wonder why the below equation only workable in expressions of chart but not in the script. Please assist to check if the below formula is correct.

Count(DISTINCT {<Part# -= {'No_Parts'}>} Job#)*12/sum( aggr( rangesum( above( sum( [Sales Qty]),0,12) ),(Monthname,(NUMERIC, ASCENDING))))

Thank you.

Re: How to load multi dimension cross table into QV?

sunny_talwar — Wed, 06 Dec 2017 00:51:05 GMT

What will be the dimension for the chart you are going to be using this expression in?

Re: How to load multi dimension cross table into QV?

wendytham — Wed, 06 Dec 2017 00:55:33 GMT

Hi Sunny,

Dimension will be Month (in MMM format).

If the Financial Year period is as below, and I would like to select the year by Financial Year, how to set it?

04/2016 to 03/2017 = FY2016

04/2017 to 03/2016 = FY2017

Thank you.

Re: How to load multi dimension cross table into QV?

sunny_talwar — Wed, 06 Dec 2017 01:00:36 GMT

You can check here for Fiscal and Standard Calendar generation

Re: How to load multi dimension cross table into QV?

wendytham — Wed, 06 Dec 2017 01:01:47 GMT

Ok, thanks. How about the market ratio formula?

Re: How to load multi dimension cross table into QV?

sunny_talwar — Wed, 06 Dec 2017 01:04:25 GMT

I wonder how you will use RangeSum(Above()) if Month is the only dimension... are you sure you mean Month or do you mean MonthYear (a field which has both month and year)?

Re: How to load multi dimension cross table into QV?

wendytham — Wed, 06 Dec 2017 01:15:43 GMT

Sorry, i meant MonthYear. The market ratio (in%) need to plot in line graph. TQ.

Re: How to load multi dimension cross table into QV?

sunny_talwar — Wed, 06 Dec 2017 01:19:54 GMT

Then I think the expression looks good to me....

Re: How to load multi dimension cross table into QV?

wendytham — Wed, 06 Dec 2017 01:23:35 GMT

But I wish to create a variable in script for this market ratio as I need to drill down the market ratio for all the Model# which is not show in the attached data.

Re: How to load multi dimension cross table into QV?

wendytham — Wed, 06 Dec 2017 05:42:53 GMT

Do you mind to share the qvw file?

Re: How to load multi dimension cross table into QV?

sunny_talwar — Wed, 06 Dec 2017 09:39:30 GMT

Share qvw file? Which qvw?