Need to get Index variable (quantity)

[#1]

Hello,

I'm having problem when trying to make a Index variable to my QlikView data.

I found this recent post by AngelaEcheverri56/ Stefan Stoichev really helpful - many thanks for that!

http://community.qlik.com/message/121531

But unfortunately I couldn't implement it succesfully to my QlikView script.

See my attached current QV-script. In script the main data is loaded from separate files for each year 2009, 2010 and 2011.

Problem seems to be, that when using above mentioned post's script lines (variables changed as my own's):

if(Tuote = peek(Tuote), ([Laskutus kpl] - peek([Laskutus kpl]))/peek([Laskutus kpl])) as Var,

if(Tuote = peek(Tuote), (1+Var)*peek(Index),100) as Index

(Here 'Tuote' means in finnish same as Product, and 'Laskutus kpl' quantity in english - so I want to make here an Index from quantity/ volume, not price

--> 1.) It tends to give me error message, that it doesn't recognise Var (Field not found - <Var>), when I but the lines inside the load-statement, where

         I load data from some of the "LaskutusSiirtoFakta.csv"-files

      2.) Or the script don't give me any error messages and the new variables don't show up, when i put the script lines to the end of the script.

I also attached little example of my excel-data. There are only first and last rows of the data and some columns has been erased.

Where do I need to put these script lines and how do I have to change them so, that the index 100 = first month of the data (january 2009)?

And the index=100 has to be for every product. And do I need to concatenate my tables?

Would really appreciate help.

[#2]

Hello Mika,

I didn't find the right place were to put the lines mentioned above, but I think I can help you anyway.

Mika Rinne-Kanto schrieb:
--> 1.) It tends to give me error message, that it doesn't recognise Var (Field not found - <Var>), when I but the lines inside the load-statement, where
         I load data from some of the "LaskutusSiirtoFakta.csv"-files
      2.) Or the script don't give me any error messages and the new variables don't show up, when i put the script lines to the end of the script.     

To point you into the right direction, lets have a look to the two script lines of your post:

In the first script line "if . . . as Var," this Var is quite another Var than in the following line "if. . .  (1+Var)*peek(Index) . .  .". As you can see in the original excel-file exists a column called Var. In the second line QV refers to that excel column, NOT to your Var in the line above. And because you do not have a (excel-)column called Var in your source-file you get the errror message "Field not found <Var>".

If you need the expression Var from the first line within the second line replace the Var in the second line with the whole calculation similar to this:

. . .  (1+Var)*peek(Index) . . . -- >

. . . (1+([Laskutus kpl] - peek([Laskutus kpl]))/peek([Laskutus kpl])) * peek(Index) . . .

HtH

Roland

[#3]

Hello Roland,

And first sorry for the late reply. Thanks for the helpful answer. Now I managed to get that Index variable show up. I only got it work when I put that line right after the LaskutusSiirtoFakta.csv -load statement (after the last variable [Laskutus kpl]). But no matter after which of the three LaskutusSiirtoFakta.csv -load statements I put it, it puts year 2011 as Index-base, so no year 2009 (?). And it only shows up Index value 100 and there's no movement in that variable in diffenent months. So I would like to get index value 100 to january 2009 for every product.

With best regards,

Mika

[#4]

Hello Mika,

no problem with your late reply. Most of us have to do other things time by time . . .  😉

Don't know if you fixed it all meanwhile. If not, please post an example app to show what are you struggling with. As we say (german): A picture tells more than thousand words.

RR