topic Re: Qlikview - data from excel in QlikView

Qlikview - data from excel

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

Hi all,

i have specific table that i fill every day and i need to create bar chart from this table.

My table loook like this:

Column C,D,E,F present one week in month and i need to fill data from SmallPlace, BigPlace,All and Date. Column G,H,I,J present next week with data.

I need to load this excel in qlikview and

- make chart that have x-axis column smallPlace,BigPlace and sum of

row,

- also need chart that can filter by date,

- chart for sum of all total by day,

- chart for all smallPlace,BigPlace from column C,D ,G,H.

I don't have any idea. Can I create chart with table like this or I need to change my table???

Re: Qlikview - data from excel

Anonymous — Mon, 01 May 2017 13:06:43 GMT

first you'll need to transform your excel data... please upload an excel file with sample data...

Re: Qlikview - data from excel

Anonymous — Tue, 02 May 2017 07:25:16 GMT

I attached excel file

Re: Qlikview - data from excel

Frank_Hartmann — Tue, 02 May 2017 08:54:36 GMT

If you only have the Data for 2 weeks in your excel as shown in your screenshot above then have a look at the attached sample.

But if there are more weeks, means that your excel expands week by week then you will have to look for some advanced scripting for transposing your table. Maybe some Pro´s are able to help!

hope this helps

Re: Qlikview - data from excel

beck_bakytbek — Tue, 02 May 2017 11:03:39 GMT

Hi ja ja,

for data-transformation look at this: https://www.youtube.com/watch?v=1xXcoi9buLM

i hope that helps

Beck

Re: Qlikview - data from excel

Anonymous — Tue, 02 May 2017 18:41:19 GMT

this should work, the rest should be no problem:

TEMP:
LOAD * FROM [Book6.xlsx] (ooxml, embedded labels, table is Sheet1);

DROP Fields Day;
RENAME Field SmallPlace to SmallPlace0;
RENAME Field All to All0;
RENAME Field BigPlace to BigPlace0;
RENAME Field Date to Date0;

for i=0 to (Div(NoOfFields('TEMP')-1,4) -1)
      DROP Field All$(i);
      LOAD
           RowNo() as ##,
           Location,
           SmallPlace$(i) as SmallPlace,
           BigPlace$(i) as BigPlace,
           Date(Date$(i)) as Date
      Resident TEMP Where Location<>'Total';
NEXT

DROP Table TEMP;

Re: Qlikview - data from excel

Frank_Hartmann — Fri, 05 May 2017 07:38:18 GMT

If your issue is solved please mark Robins answer as the correct one.

If not, then please tell us your problem you are facing!

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Qlikview - data from excel

Anonymous — Fri, 05 May 2017 11:14:33 GMT

Thanks a lot!!!!!