Is it Possible to load the attached excel and create a chart exactly in the same format as in the excel

[ramyasaiqv]

Hello Community,

First of all i really thank and appreciate the help that's i'm getting from the community. The community was extremely helpful and i really thank each and every one who helped me with my past queries.

Here again i have another query related to an excel data conversion.

I know how to use cross table prefix but the attached excel is very unstructured and i'm not able to identify a way to load the data and create a chart in QlikView/Qlik Sense  exactly (same format) as in the excel. Please can some one help/suggest me if that is indeed possible.

Ramya

[kaushiknsolanki]

How it is different from the one which you asked on below post.

Index table in Qlik Sense

Regards,

Kaushik Solanki

[ramyasaiqv]

Hi Kaushik,

Thank you for the response.

The Output chart format is same but the source (input excel) format changed. Previously those 3 metrics MXT, RXT and TXT were coming from 3 different excels. But now all the 3 metrics are coming from the same excel (as shown in the attached excel all those 3 metrics are headers in the excel).When i tried with cross table prefix i'm getting only one metric  but the requirement is need 3 metrics as shown in excel. I hope the requirement is clear and makes sense . Thank you,

Ramya

[ramyasaiqv]

Kaushik,

and one more thing is i'm trying to implement this is Qlik Sense.

Thank you,

Ramya

[mdmukramali · Accepted solution]

Dear Ramya,

this is the one way to achieve the result.

kindly find the attached  file.

CrossTable(Quarter, Value, 3)

LOAD F1 as No,

     F2 as Item,

     'RXT' as Flag,

     Q1,

     Q2

FROM [lib://Downloads (qlik-pc_qlik)/Index_2016.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Indices);

CrossTable(Quarter, Value, 3)

LOAD F1 as No,

     F2 as Item,

     'UXT' as Flag,

     Q12 as Q1,

     Q22 as Q2

FROM [lib://Downloads (qlik-pc_qlik)/Index_2016.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Indices);

CrossTable(Quarter, Value, 3)

LOAD F1 as No,

     F2 as Item,

     'MXT' as Flag,

     Q11 as Q1,

     Q21 as Q2

FROM [lib://Downloads (qlik-pc_qlik)/Index_2016.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Indices);

CrossTable(Quarter, Value, 3)

LOAD F1 as No,

     F2 as Item,

     'Total Average' as Flag,

     Q13 as Q1,

     Q23 as Q2

FROM [lib://Downloads (qlik-pc_qlik)/Index_2016.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Indices);

[ramyasaiqv]

Hi Mukram,

Thank you for the response will try on my machine.

Ramya

[ramyasaiqv]

It worked. Thank you so much Mukram.

Ramya