Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
777proba777
New Contributor III

Qlikview - data from excel

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:

Untitled.png

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

Tags (3)
1 Solution

Accepted Solutions
robin_hausdoerfer
Valued Contributor III

Re: Qlikview - data from excel

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;

7 Replies
robin_hausdoerfer
Valued Contributor III

Re: Qlikview - data from excel

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

777proba777
New Contributor III

Re: Qlikview - data from excel

I attached excel file

Frank_Hartmann
Honored Contributor

Re: Qlikview - data from excel

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

Re: Qlikview - data from excel

Hi ja ja,

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

i hope that helps

Beck

robin_hausdoerfer
Valued Contributor III

Re: Qlikview - data from excel

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

Re: Qlikview - data from excel

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

777proba777
New Contributor III

Re: Qlikview - data from excel

Thanks a lot!!!!!