Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ramyasaiqv
Creator II
Creator 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
Specialist III
Specialist III

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

View solution in original post

6 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Index table in Qlik Sense

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
ramyasaiqv
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Kaushik,

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

Thank you,

Ramya

mdmukramali
Specialist III
Specialist III

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
Creator II
Creator II
Author

Hi Mukram,

Thank you for the response will try on my machine.

Ramya

ramyasaiqv
Creator II
Creator II
Author

It worked. Thank you so much Mukram.

Ramya