topic How to search a value inside a table in QlikView

How to search a value inside a table

Mon, 16 Nov 2009 23:48:59 GMT

Hi,

I have the following two tables:

qualify *;

unqualify Article;

MEDIUMCOST:

load * inline

[

Article, Year, Cost

123, 2009, 100

123, 2008, 120

456, 2007, 68

456, 2006, 50

];

SALES:

load * inline

[

Article, Year, Price

123, 2009, 500

123, 2008, 240

456, 2009, 90

456, 2007, 60

456, 2005, 30

]

;

Now I have to calculate the cost and i can have the following cases:

- I have sold a product in one year and i have the evaluation of the cost for this year (product 123 sold in 2009 the cost for the year 2009 is 100)

In this case i should see the cost of the year that match, so 100

- i have sold a product in one year and i have the value of the cost in one of the previous year (product 456 sold in 2009 the cost are available only in 2007 and 2005)

in this case I should see the first cost available in the first year available. In my example the cost of product 456 in Year 2007 = 68

- i have sold a product in one year and i have no value of cost in the previous years (product 456 sold in 2005)

In this case I should have cost = 0

Any suggestion?

Thanks!!

How to search a value inside a table

sparur — Tue, 17 Nov 2009 00:49:33 GMT

You should have an interval dates for cost value by Product (Article). For example:

load * inline

[

Article, YearStart, YearEnd, Cost

123, 2009, 2099, 100

123, 2008, 2008, 120

456, 2007, 2099, 68

456, 2006, 2006, 50

];
The field "YearEnd" you may calculate from your field "Year".

then after you will get such table you should use intervalmatch() function.(see more details in help)

How to search a value inside a table

rwunderlich — Tue, 17 Nov 2009 14:21:01 GMT

sparur has sent you on the right track but I've attached an example because there are a number of steps.

-Rob

How to search a value inside a table

Tue, 17 Nov 2009 17:45:04 GMT

Hi Rob,

what to say? Thanks a lot!

I will put your solution in my model.

Andrea