topic How to load (2 X 2) Multidimensional grid? in QlikView

How to load (2 X 2) Multidimensional grid?

shumailh — Thu, 29 Jul 2010 16:17:43 GMT

Hi all,

I have a grid as attached with 2 X 2 dimensional data and I want to load it to qlikview. I tried cross table but cross table can only cross first layer of column from the above. i want to corss 2 layers as transpose.

Try loading it into qlikview you will find my issue. please help?

This is one step ahead of the question i have asked earlier, for more reference please see http://community.qlik.com/forums/t/31642.aspx

Shumail

How to load (2 X 2) Multidimensional grid?

wizardo — Fri, 30 Jul 2010 08:49:38 GMT

hmm..

i think what you need to do is to read the table twice

1st pass read only actual valus

2nd read only target values

make a crosstable on each

and join them toghter

Mansyno

How to load (2 X 2) Multidimensional grid?

Fri, 30 Jul 2010 09:32:19 GMT

Hi Shumail,

here are one idea:

- Enable Transformation Step
- Delete Target Column firts as example
- Delete the row which contains Actual and Target
- NEXT

CROSSTABLE:
- 2 Qualifier fields
- Attribute Name: Date
- Data Name: Actual
NEXT

See the application .

Good luck!

Rainer

How to load (2 X 2) Multidimensional grid?

shumailh — Fri, 30 Jul 2010 15:08:22 GMT

Hi Mansyno,

Seems like ur suggestion will might work.. I will try and let you know.

Shumail

How to load (2 X 2) Multidimensional grid?

shumailh — Fri, 30 Jul 2010 15:58:58 GMT

Hi Mansyno,

Can you kindly implement ur suggestion on qlikview with the data i shared in my earlier post?

Shumail

How to load (2 X 2) Multidimensional grid?

shumailh — Fri, 30 Jul 2010 16:07:39 GMT

Hi Rainer

Thanks for the reply, the solution u provided is working fine but it's more hardcoded as everymonth i need to change the script.



TempTarget:
CrossTable(Date, Target, 2)
LOAD
Type, Product, [Jan-2009], [Feb-2009], [Mar-2009], [Apr-2009],
[May-2009], [Jun-2009], [Jul-2009], [Aug-2009],
[Sep-2009], [Oct-2009], [Nov-2009], [Dec-2009]
FROM
 Test_5F00_Data.xls(biff, embedded labels, table is Data$,
filters(Remove(Row, Pos(Top, 2)),
Remove(Col, Pos(Top, 25)),Remove(Col, Pos(Top, 23)),
Remove(Col, Pos(Top, 21)),Remove(Col, Pos(Top, 19)),
Remove(Col, Pos(Top, 17)),Remove(Col, Pos(Top, 15)),
Remove(Col, Pos(Top, 13)),Remove(Col, Pos(Top, 11)),
Remove(Col, Pos(Top, 9)),Remove(Col, Pos(Top, 7)),
Remove(Col, Pos(Top, 5)),Remove(Col, Pos(Top, 3))));

Usually what i was doing in 1 X 1 grid, i transpose the grid first and then use the cross table option because when i cross table on month fields then it will limit them, in excel my users add new month year column on monthly basis. so in ur solution i need to re script the code every month. the problem here i am facing that it's 2 dimenstional from both side.

Is there any other way?