Excel File Data

[#1]

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

[effinty2112 · Accepted solution]

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

[#3]

Thanks a lot andrew

[effinty2112]

You're very welcome my friend!

[#5]

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.

[#6]

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

[effinty2112]

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

[effinty2112]

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

[#9]

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

Appreciate your help.

Cheers