Qlikview - data from excel

[Anonymous]

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???

[Anonymous]

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

[Anonymous]

I attached excel file

[Frank_Hartmann]

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

[beck_bakytbek]

Hi ja ja,

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

i hope that helps

Beck

[Anonymous · Accepted solution]

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;

[Frank_Hartmann]

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

[Anonymous]

Thanks a lot!!!!!