Show valid row for date

mike_spada — Wed, 25 Nov 2020 16:16:04 GMT

Hi to all,

I cannot find a solution to this scenario.

I have a table with product code, supplier code, date, percentual 1, percentual 2, like this:

|1|A001|20090303|100|0|

|1|B001|20090303|0|100|

|2|C001|20160501|0|95|

|2|C001|20160501|100|5|

I want to have a straight (or pivot) table with dimensions code and supplier, then a variable (vMyDate, for example 20160630) where I can specify a date.

perc1 is the percentual in use in the period previous to date (-1), and perc2 is the percentual in use in the period after the date.

So I must see:

with vMyDate = 20160412:

|1|B001|20090303|0|100|

|2|C001|20160501|100|5|

with vMyDate = 20160630:

|1|B001|20090303|0|100|

|2|C001|20160501|0|95|

Anyone can help me to understand how I can build it?

Thanks!

Mike 🙂

Re: Show valid row for date

swuehl — Tue, 12 Apr 2016 14:42:38 GMT

Not 100% sure I understand your setting.

Which record is to take if you have multiple records per code on the same date, both showing values for e.g. perc2?

Why are you chosing |2|C001|20160501|0|95| in your example for vMyDate = 20160630?

Maybe have a look at

Creating a Date Interval from a Single Date

and try frist to create distinct intervals per code with the percentage value you want to apply to that interval.

topic Show valid row for date in QlikView

Show valid row for date

Re: Show valid row for date