Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
i have an excel of the follwoing format:
Date | Department A | Date | Department B | Date | Department C |
---|---|---|---|---|---|
1-1-13 | 100 | 1-1-13 | 111 | 2-2-12 | 200 |
2-1-13 | 110 | 3-1-13 | 112 | 12-1-13 | 500 |
Now i need to read this into qlikview to get out put as below:
Date | Department | Amount |
---|---|---|
1-1-13 | A | 100 |
2-1-13 | A | 110 |
1-1-13 | B | 111 |
3-1-13 | B | 112 |
2-2-12 | C | 200 |
12-1-13 | C | 500 |
How do i do that?
Thanks.
Put the below code in the QV file..
t1:
LOAD Date(@1,'MM-DD-YYYY') as Date,
num(@2) as Amount,
right(@3,1) as Department
FROM
tableformat.xlsx
(ooxml, explicit labels, table is Sheet1, filters(
Unwrap(Col, Pos(Top, 3)),
Unwrap(Col, Pos(Top, 3)),
Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),
ColXtr(1, RowCnd(CellValue, 1, StrCnd(null)), 0),
Replace(3, left, StrCnd(null)),
Replace(3, top, StrCnd(start, 'D', not)),
Remove(Row, RowCnd(Compound,
RowCnd(CellValue, 1, StrCnd(equal, 'Date')),
RowCnd(Interval, Pos(Top, 2), Pos(Top, 1), Select(1, 0))
)),
Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Date')))
));
Regards,
Sushil
Hi,
you can use the utiltity in the edit script "Enable transformation" which you will get when you do next when you select the table from the edit script..
t1:
LOAD Date(@1,'MM-DD-YYYY') as Date,
@2 as Department
FROM
tableformat.xlsx
(ooxml, explicit labels, table is Sheet1, filters(
Unwrap(Col, Pos(Top, 3)),
Unwrap(Col, Pos(Top, 3)),
Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),
Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Date')))
));
Below is the attached xl file and appliction.
HTH
Sushil
thanks Sushil for your response. but what i wanted is a bit different. i wanted one new column as 'Amount' to be generated and the department names to be there under the department field(please refer the above post).
Thanks.
Hi,
you have this standard Dep. or more.
find the attached file:
Kumar,
this is so manual! i am just trying to avoid this. Something automatic and dynamic would be required, because my excel book would contain multiple sheets and many more departments(it may increase dynamically). Anyway, thank you for your input.
Thanks.
hi,
Please find attached.
HTH
Sushil
to get the only A,B, C from the excel then use
right(@3,1) as Department
in the script.
is this app using the same excel as source?
Hi sushil kumar,
i don't know fully about "Enable Transformation Step" . so where i can get good tutorialspoint about this
yes,
It is using same excel as source.