Excel sheet into Qlikview

[#1]

Hello Guys,

I am trying to load this excel sheet into Qlikview. The problem here is with the Cross table. Can anyone give a tip, so that I can load it with Months as a field and also the Ownplanned, Ownactual, Tempplanned, Tempactual etc as a field. May be I have to manipulate it a bit in excel to get it in Qlikview. If it does not take much time. I will appreciate a example to load it into Qlikview.

Thanks in advance

Sravan

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/5314.Personal.xls:550:0]

[nathanfurby]

Couldn't you use the 'Crosstable' option in the Table File Load Wizard?

[#3]

Hello Nathan,

I used and it generates for Month and the values like this

crosstable(Months, Data, 4). As you see in Excel it also has ownactual, ownplanned etc. If there is one dimension like Months and Values it works but how to use with two dimensions like Months, Ownactual, DATA. I hope I am clear.

Thanks

Sravan

[#4 · Accepted solution]

It's not a very polished solution but you could try something like:

//initial file load

Source:
LOAD *
FROM
"C:\My Documents\My Data Sources\5314.Personal[1].xls"
(biff, no labels, header is 3 lines, table is Cijfers$)
WHERE(@1 > 0);
 //do this 12 times, once for each month)
 //**************month block 1
 set month=month(makedate(2010,$(m),1));
 Monthlydata:
 Load
 @1 as id1,@2 as id2,@3 as id3,@4 as Line,
 $(month) as Month,
 //january fields
 @5 as OP,
 @6 as OA,
 @7 as TP,
 @8 as TA
 resident Source;

 //**************month block 2
 set m=($(m)+1);
 set month=month(makedate(2010,$(m),1));
 Load
 @1 as id1,@2 as id2,@3 as id3,@4 as Line,
 $(month) as Month,
 //february fields
 @9 as OP,
 @10 as OA,
 @11 as TP,
 @12 as TA
 resident Source;

 //*************month block 3

//******* you get where this is going, just copy and paste month block 2 and add 4 to the field numbers
drop table Source;

It only hits the harddrrive once doing the initial data load, then derives 12 month tables from memory, concatanates them (gotta love QV's autoconcatanate) the drops the original table to free the memory.

you will obviosuley need to finish off the month blocks and repoint the initial file laod to where ever you store thee xcel file but it should give you the transaction style you want (although probably just the same effort required to manipulate the data in excel by hand lol)

[#5]

Hi SQl-Cowboy,

Thanks for your answer. I have a Problem here. You did it dynamically but It seems to be not right or may be I am not able to únderstand it.

Your Idea is to group the key figures to every month.Thats why you give

For Jan: ( I guess m is taken as 1 for the first time)

set month=month(makedate(2010,$(m),1));

After that m increases by 1 for every month. It never stops and that is wrong. It must stop when the Month is 12.

when I reload, I see Month field changing for every reload and after 12 months it stops. That makes sin as there are only 12 months.

Any Idea how to stop this. I tried this but somehow it is not working 😞

Source:
LOAD *
FROM
"C:\Users\Puppala_S\Desktop\Personal.xls"
(biff, no labels, header is 3 lines, table is Cijfers$)
WHERE(@1 > 0);

For m = 1 to 12 step 1
 //do this 12 times, once for each month)

set n = 5;

For $(n) = 5 to 45 step 4


 //**************month block 1
 set month=month(makedate(2010,$(m),1));
 Monthlydata:
 Load
 @1 as id1,@2 as id2,@3 as id3,@4 as Line,
 $(month) as Month,
 //january fields
 @$(n) as OP,
 @$(n)+1 as OA,
 @$(n)+2 as TP,
 @$(n)+3 as TA
 resident Source;

Next

Next

Thanks
sravan

[#6]

Hi Sraven,

I see you are trying to increment the field number dynamically usuing a for next/loop, in my experience this does not work as QV will expand the expression

@$(n)+1 as OA

into

(@5+1) as OA

therfore if the data in field 5 equalled 3.30, QV would inaccurattely load it as

(3.3+1) as OA

which woudl show as 4.3 (you are instructing it to add 1 to every value in the field)

With that in mind it is not possible to use a loop to dynamically allocated the fields, therefore this entire section

//**************month block 2
set m=($(m)+1);
set month=month(makedate(2010,$(m),1));
Load
@1 as id1,@2 as id2,@3 as id3,@4 as Line,
$(month) as Month,
//february fields
@9 as OP,
@10 as OA,
@11 as TP,
@12 as TA
resident Source;

must be manually repeated within the script to be executed a total of 12 times, with the fields @9-@12 manually incremented each time.

The original script i submitted will indeed work however only contains enough for two months of data, you would need to copy and paste the section above a further 10 times and add 4 to the field numbers manually to gain a fully working script

Taking this onboard, do you now see why the line

set m=($(m)+1);

is needed, as there is no "next", we need something to increment the month counter each time we load the OP,OA,TA and TP fields.

I think you have the correct idea but are trying to shortcut the process which i don't think will work in this case.

[#7]

Hi,

Thanks for your response. For....Next does not work. That I understood.

I did what you said to do. I expanded the script to 12 months.The problem what I had and now have is.

-When I reload the script once the month field shows 12 months and it works properly. When I reload it again the m goes on increasing. I need to set the Limit here and dont know how. Attached is the file.

Thanks

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.00.49.52/test123.qvw]

[#8]

Solved ..silly but I just needed to set m= 1;

at the end. That did the trick.

Thankyou for your patience SQL-Cowboy

[#9]

Yes, you are correct setting m=1 at the start of the script will ensure you only load 1 to 12 on reload, my apologies for not including that part in my first post.

Glad I could help!