Inner join tables

[Zachary]

Hello,

I'm new to Qlikview and need help solving my task.

I want modify the current data model to now include a new column, "StandardPrice", in the row value.

The column is the price for some item at the time sold it was sold to the customer.
So the item in the sales order line has a one-to-many relationship to the table ItemCalculation.

The ItemCalculation table has, among others, the following columns defined:
Item
ValidDate
StandardPrice

How must I proceed to achieve my goal?

Thanks in advance for the help.

[rubenmarin · Accepted solution]

Hi @Zachary, to assing values you can use IntervalMatch. https://help.qlik.com/en-US/sense/September2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPre...

But interval need an start and end point of each date range, so first you need to cerarte those range, taking ValidDate as the start date you can create the end date using peek(), ie:

PricesWithIntervals:
LOAD Item, 
  StandarPrice,
  ValidDate as StartDate,
  If(Peek(Item)=Item, Date(Peek(StartDate)-1), Date(99999) as EndDate
Resident [ItemCalculationTableName]
Order by Price, ValidDate desc;