Join 2 tables

[mohan2391]

I have 2 tables. One contains monthly Price and other table contains multiplier details(and from when the multiplication should happen). I don’t have each month entry in my 2nd table.

Please find the attached screenshot, I need the output like that. 

Price * Multiplier for every month

how can I achieve it ?

 Thanks in advance

[JandreKillianRIC · Accepted solution]

Hi @mohan2391 

Something like this 

This is a Interval Match. 

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptP...

Here is the script below. 

// Load Table2 with Date and Price
Table2:
Load 
    Date(Date#(Date, 'YYYYMMDD')) as Date, 
    Price;
Load * Inline [
    Date, Price 
    20230101,50
    20230201,51
    20230301,52
    20230401,50
    20230501,51
    20230601,52
    20230701,50
    20230801,51
    20230901,52
    20231001,50
    20231101,51
    20231201,52
    20240101,50
    20240201,51
    20240301,52
    20240401,50
    20240501,51
    20240601,52
    20240701,50
    20240801,51
    20240901,52
    20241001,50
    20241101,51
    20241201,52
];

// Load Table1Tmp with StartDate and Value
Table1Tmp:
Load * Inline [
    StartDate, Value 
    20230101,100
    20240601,1
];

// Create Table1 with StartDate, EndDate, and Value
Table1:
Load 
    Date(Date#(StartDate, 'YYYYMMDD')) as StartDate,
    IF(Isnull(Previous(StartDate)), Date(YearEnd(Today())),  
        Date(MonthEnd(Date#(Previous(StartDate), 'YYYYMMDD'))-1)) as EndDate,
    Value
Resident Table1Tmp
Order By StartDate Desc; 

Drop Table Table1Tmp;

// Perform IntervalMatch to link Table2.Date with Table1.StartDate and Table1.EndDate
IntervalMatch:
Inner Join IntervalMatch (Date)
Load 
    StartDate,
    EndDate
Resident Table1;

ResultTableTmp:
NoConcatenate
Load 
	*
Resident Table1; 

Join (ResultTableTmp)
Load 
	*
Resident Table2; 

Drop Table Table2, Table1;

Load 
	*, 
    Price * Value as Result
Resident ResultTableTmp;

Drop Table ResultTableTmp; 

Regards - Jandre

[Bhushan_Mahajan]

@mohan2391 You can also use peek function. refer below blog.

First create calendar based on your table2 date then join it with rate table the refer below blog.

https://community.qlik.com/t5/Design/How-to-populate-a-sparsely-populated-field/ba-p/1470637

[mohan2391]

Thank You for your prompt resonse and it worked for me. 😊🙏🏻

[JandreKillianRIC]

Hi @mohan2391 

Thats awesome. Check out that link for Intervalmatch. Its a really powerful function! 

Regards - Jandre

[mohan2391]

Thank You Bhushan for your response. Looks like it does also work in my case, but am going with other approach.

Maybe whoever come across this request can try any of these 2 answers provided.