Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Two structures in the same excel file

Hi everybody, I have many excel files in a folder, the problem is that in this files, I have two different structures, I would like to load data from this files:

Example:




City ACity BCity CCity D
ProduitCentreStock22283440
aZIKXJr13333
aZIKXJr24444
aZIKXJr35555
aZIKXJr46666
aZIKXJr57777
aZIKXJr68888
aZIKXJr79999
aZIKXJr810101010
aZIKXJr911111111
aZIKXJr1012121212
aZIKXJr1113131313
aZIKXJr1214141414
aZIKXJr1315151515
bZIKXStock16161616
bZIKXJr116161616
bZIKXJr217171717
bZIKXJr318181818
bZIKXJr419191919
bZIKXJr520202020
bZIKXJr621212121
bZIKXJr722222222
bZIKXJr823232323
bZIKXJr924242424
bZIKXJr1025252525
bZIKXJr1126262626
bZIKXJr1227272727
bZIKXJr1328282828

The result that I would like to have is:

ProduitCentreCityStockJr1Jr2Jr3Jr4Jr5Jr6Jr7Jr8Jr9Jr10Jr11Jr12Jr13
aZIKXA223456789101112131415
aZIKXB283456789101112131415
aZIKXC343456789101112131415
aZIKXD403456789101112131415
bZIKXA1616171819202122232425262728
bZIKXB1616171819202122232425262728
bZIKXC1616171819202122232425262728
bZIKXD1616171819202122232425262728

Thank you.

Zak

1 Solution

Accepted Solutions
whiteline
Honored Contributor II

Re: Two structures in the same excel file

Hi, zykooo88.

Use crosstable(Value, City, 3) statment (look at help). It allows you to load your data like a straight table with City as another field.

Then you can easily create a Chart with Produit, Centre, City, Stock as dimensons and Sum(value) as expression.

2 Replies
Not applicable

Re: Two structures in the same excel file

If your question is achive the 2nd table format in Qlikview then here is one of the solution.

Have Dimensions as: Produit, Centre, Stock

Have 4 Expressions for each City: Sum([City A]), Sum([City B]), Sum([City C]), Sum([City D])

You can achive this by Pivot Table.

TableStruct.jpg

If your questions is more about loading then scirpt in different way, then it has to be handled at the LOAD Script level. Using TRANPORSE option.

whiteline
Honored Contributor II

Re: Two structures in the same excel file

Hi, zykooo88.

Use crosstable(Value, City, 3) statment (look at help). It allows you to load your data like a straight table with City as another field.

Then you can easily create a Chart with Produit, Centre, City, Stock as dimensons and Sum(value) as expression.

Community Browser