topic Re: Excel File Data in QlikView

Excel File Data

Fri, 18 Dec 2015 06:44:24 GMT

Hi All,

Please find the attached Excel.

I need the data something like below when i load.

Product:

Project 1

Project 2

Code

0011

0022

Specs

Green

Orange

Purple

Price

All the Prices in one column.

Please help me with this.

Regards,

Keerthi KS

Re: Excel File Data

effinty2112 — Fri, 18 Dec 2015 08:24:23 GMT

Hi Keerthi,

This script

Data:

LOAD Code as Code1,

Specs,

Price,

F4 as Product,

F5 as Code

FROM

Community.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(

Remove(Col, Pos(Top, 17)),

Remove(Col, Pos(Top, 16)),

Remove(Col, Pos(Top, 15)),

Remove(Col, Pos(Top, 14)),

Remove(Col, Pos(Top, 13)),

Remove(Col, Pos(Top, 12)),

Remove(Col, Pos(Top, 11)),

Remove(Col, Pos(Top, 10)),

Remove(Col, Pos(Top, 9)),

Remove(Col, Pos(Top, 8)),

Remove(Col, Pos(Top, 7)),

Remove(Col, Pos(Top, 6)),

Remove(Col, Pos(Top, 5)),

Remove(Col, Pos(Top, 4)),

Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

ColXtr(2, RowCnd(CellValue, 2, StrCnd(start, 'Project')), 0),

ColXtr(1, RowCnd(CellValue, 2, StrCnd(start, 'Project')), 0),

Replace(4, top, StrCnd(null)),

Replace(5, top, StrCnd(null)),

Remove(Row, RowCnd(CellValue, 3, StrCnd(null)))

));

Gives you this table

Code	Code1	Price	Product	Specs
0011	D50	5.690000	Project1	Green
0011	D50	6.290000	Project1	Orange
0011	D50	6.880000	Project1	Purple
0011	P75	8.200000	Project1	Orange
0011	P75	9.600000	Project1	Purple
0022	C20	2.020000	Project2	Green
0022	C20	2.300000	Project2	Orange
0022	D30	2.750000	Project2	Green
0022	D30	3.100000	Project2	Orange
0022	D35	3.520000	Project2	Green
0022	D35	3.970000	Project2	Orange
0022	P60	6.700000	Project2	Green
0022	P60	7.400000	Project2	Orange

It would be worth your while learning how to master the file wizard. It's a bit awkward but with practice and patience you'll get to grips with it.

Cheers

Re: Excel File Data

Fri, 18 Dec 2015 09:03:22 GMT

Thanks a lot andrew

Re: Excel File Data

effinty2112 — Fri, 18 Dec 2015 10:01:05 GMT

You're very welcome my friend!

Re: Excel File Data

Fri, 18 Dec 2015 10:30:34 GMT

Is there any video or something where its explained clearly on the excel data.

I have many data. Want to learn how to modify for qlikview.

Re: Excel File Data

Fri, 18 Dec 2015 10:42:12 GMT

I am not able to do it for my data in the wizard

Re: Excel File Data

effinty2112 — Fri, 18 Dec 2015 10:48:40 GMT

Hi Keerthi,

Please find qvw file, hope this helps. Send me more data if possible and I will work on it though I am a bit short of time today, it may be tomorrow before I can do it.

Andrew

Re: Excel File Data

effinty2112 — Fri, 18 Dec 2015 11:48:50 GMT

Hi Keerthi,

I've been working on this in my office but I need to go soon. A bus is arriving in 5 minutes to to us to our Christmas party. If I keep a clear head I'll work on this at home tonight. If I don't have a clear head I'll try to make time to do work on it tomorrow.

Cheers

Andrew

Re: Excel File Data

Fri, 18 Dec 2015 12:09:58 GMT

Thats ok Andrew. Enjoy Your Party I somehow tried and i am able to do it. Thanks for your support.

Appreciate your help.

Cheers