How to load multi dimension cross table into QV?

[wendytham]

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

[sunny_talwar]

May be this

The Crosstable Load

[devarasu07]

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

[chriscammers]

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

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

[wendytham]

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

[sunny_talwar]

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

[wendytham]

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.

[sunny_talwar · Accepted solution]

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;

[wendytham]

It works! Thank you so much Sunny!

[wendytham]

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.