Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm trying to get next content read from Excel spreadsheet into the Qlikview Cloud:
The point here is I would like to have two different logic tables, or at least one with a new field let's say "Source" which would have two values 'Rent' and 'Empty' for every row in the table according with what's shown in the picture.
The first problem I've faced is Qlikview treats it's column separately, deleting every empty cell, so try to imagin this picture without the empty cells and you'll finde out how format is changing if your trying to retrieve every value with the "fieldvalue" statement.
My second problem goes with the value treatment inside qlikview. In the picture, ID=3 goes in both Rent and Empty "chapters" of the table. But trying to get the second 3 with fieldvalue(Column, Index), it's not getting the '3' but the '5' at that index cause the 3 it's alredy written in the "cloud" of Qlikview.
As far as I have not much experience reading Excel files in Qlikview, I'm open to every suggestion. Maybe there's a better way to do the reading. Any idea of how to solve this?
I'm also publishing the code I've been developing and attaching the original Excel file.
Thanks for your time and atenttion.
Kind regards,
Aitor.
The Qlikview script code:
Directory;
Input:
LOAD @1,
@2,
@3,
@4,
@5,
@6,
@7,
@8,
@9,
@10,
@11,
@12,
@13,
@14
FROM
[..\pruebas.xls]
(biff, no labels, table is Sheet1$);
for i=$(Rent)+2 to $(Empty)-1;
Rent:
Load
Num#(fieldvalue('@1',$(i)),'#.#') as ID,
fieldvalue('@2',$(i)-1) as Desc,
Num#(fieldvalue('@3',$(i)-1),'#.#') as January,
Num#(fieldvalue('@4',$(i)-1) ,'#.#')as February,
Num#(fieldvalue('@5',$(i)-1),'#.#') as March,
Num#(fieldvalue('@6',$(i)-1),'#.#') as April,
Num#(fieldvalue('@7',$(i)-1),'#.#') as May,
Num#(fieldvalue('@8',$(i)-1),'#.#') as June,
Num#(fieldvalue('@9',$(i)-1),'#.#') as July,
Num#(fieldvalue('@10',$(i)-1),'#.#') as August,
Num#(fieldvalue('@11',$(i)-1),'#.#') as September,
Num#(fieldvalue('@12',$(i)-1),'#.#') as October,
Num#(fieldvalue('@13',$(i)-1),'#.#') as November,
Num#(fieldvalue('@14',$(i)-1),'#.#') as December,
'Rent' as Origen
resident Input;
NEXT;
for i=$(Empty) to NoOfRows('Input')-2;
Empty:
Load
Num#(fieldvalue('@1',$(i)+1),'#.#') as ID,
fieldvalue('@2',$(i)-3) as Desc,
Num#(fieldvalue('@3',$(i)-3),'#.#') as January,
Num#(fieldvalue('@4',$(i)-3) ,'#.#')as February,
Num#(fieldvalue('@5',$(i)-3),'#.#') as March,
Num#(fieldvalue('@6',$(i)-3),'#.#') as April,
Num#(fieldvalue('@7',$(i)-3),'#.#') as May,
Num#(fieldvalue('@8',$(i)-3),'#.#') as June,
Num#(fieldvalue('@9',$(i)-3),'#.#') as July,
Num#(fieldvalue('@10',$(i)-3),'#.#') as August,
Num#(fieldvalue('@11',$(i)-3),'#.#') as September,
Num#(fieldvalue('@12',$(i)-3),'#.#') as October,
Num#(fieldvalue('@13',$(i)-3),'#.#') as November,
Num#(fieldvalue('@14',$(i)-3),'#.#') as December,
'Empty' as Origen
resident Input;
NEXT;