Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ramyasaiqv
Contributor II

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

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

1 Solution

Accepted Solutions
mdmukramali
Valued Contributor II

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

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);

5.PNG

6 Replies

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

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

Index table in Qlik Sense

Regards,

Kaushik Solanki

ramyasaiqv
Contributor II

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

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
Contributor II

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

Kaushik,

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

Thank you,

Ramya

mdmukramali
Valued Contributor II

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

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);

5.PNG

ramyasaiqv
Contributor II

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

Hi Mukram,

Thank you for the response will try on my machine.

Ramya

ramyasaiqv
Contributor II

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

It worked. Thank you so much Mukram.

Ramya

Community Browser