Inner join tables

Zachary — Fri, 23 Oct 2020 09:40:21 GMT

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.

Re: Inner join tables

rubenmarin — Sat, 24 Oct 2020 17:31:36 GMT

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

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;

topic Re: Inner join tables in QlikView

Inner join tables

Re: Inner join tables