Join Tables based on ranged citeria

[jorgie]

Hi to all!

I try to replicate the join I used in SQL in order to combine some data.

the code I used in qlik data load editor was

Dates:
LOAD
Date,
WeekNumber,
MonthNumber,
week4
FROM [lib://Sales_Uploads/QVD/Calendar.qvd]
(qvd);

Pan:
LOAD
weekend("From Date") AS StartingWeek,
weekend("To Date") AS EndingWeek,
"From Date",
"To Date",
"Promo Days",
Category,
Chain,
"Chain (Group)",
"Media Type",
Brand,
"Special Type",
Company,
Description,
"Price Offer",
"Price Offer Type",
"Volume Offer",
"Volume Offer Type",
"Product Offer Type",
Offer,
"Offer Percentage",
"Final Price",
"First Price",
"Unit Size",
"Entry Size"
FROM [lib://Sales_Uploads/QVD/Pan.qvd]
(qvd);

JoinTable:
LOAD *
Resident Dates;

Join (JoinTable)
LOAD *
Resident Pan
Where Date >= StartingWeek and Date <= EndingWeek;

I get the error that doesn't find temporary JoinTable

In other case I had error that Date not found

Thanks in advance for you help

[ogster1974]

Put NoConcatenate above your JoinTable: line. Qlik thinks you already have that table it can work with called Dates so doesn't create a new one with that name so when you go to Join it doesn't recognise the name you've given.

[vinieme12 · Accepted solution]

Conditional Joins are NOT POSSIBLE in QlikSense

QlikSense can only Join on EXACT VALUES

The Table Pan doesn't have the field Date to be able to do the below comparison

LOAD *
Resident Pan
Where Date >= StartingWeek and Date <= EndingWeek;

Use  IntervalMatch() instead,  refer example in help

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

[jorgie]

it fixed the JoinTable issue but 

The following error occurred:

Field 'Date' not found

The error occurred here:

Join (JoinTable) LOAD * Resident Pan Where Date >= StartingWeek and Date <= EndingWeek

thank you for your time

[jorgie]

thank you very much!!!

I am almost there....

it doesn't get the  date between but i want and the =

I might go doing a workaround remove and add a day in order to get the between case.

Still if there is a way using IntervalMatch() it would be the ideal

 thank you very much for your time and help

[ogster1974]

I'd take a different approach if you had a unqiue key to your PAN data.

I'd create a date link table between your PAN data and your calendar like this.

This will generate the between date values you seek but you need a unique key in your transaction data for it to work.  As you can see I can now select a data and it returns the transactions between it.

[jorgie]

to be honest it is not clear from the visualization that you attached, I will try it and feed back

[ogster1974]

if you replace my 'Data' table with your 'PAN' table plus identify a key field i.e. a field unique at a row level this script should  work and build that model up.

// Get date range from data and key

LET NumRows=NoOfRows('Data');

FOR i=0 to $(NumRows)-1

// Capture date range in variables
LET vKey = PEEK('Key', $(i), 'Data');
LET vFirstDate = NUM(PEEK('Date From', $(i), 'Data'));
LET vLastDate = NUM(PEEK('Date To', $(i), 'Data'));

Trace '$(vKey)';
Trace '$(vFirstDate)';
Trace '$(vLastDate)';

// Auto-generate gap-less calendar
DateLink:
LOAD
$(vKey) as Key,
DATE($(vFirstDate) + RecNo( ) -1) AS Date
AUTOGENERATE
$(vLastDate) - $(vFirstDate) + 1;

LET vFirstDate = 0;
LET vLastDate = 0;

Next;

// Create date attributes for master calendar
MasterCalendar:
LOAD
Distinct
Date,
WEEK(Date) AS Week,
YEAR(Date) AS Year,
MONTH(Date) AS Month,
DAY(Date) AS Day,
WEEKDAY (Date) AS Weekday,
'Q' & CEIL(MONTH(Date) / 3) AS Quarter,
WEEK(Date) & '-' & YEAR(Date) AS WeekYear,
Div(Date-YearStart(Date,0,4),7)+1 as FiscalWeek

RESIDENT DateLink;

[vinieme12]

Try below

Dates:
LOAD
Date,
WeekNumber,
MonthNumber,
week4
FROM [lib://Sales_Uploads/QVD/Calendar.qvd]
(qvd);

Pan:
LOAD
weekend("From Date") AS StartingWeek,
weekend("To Date") AS EndingWeek,
"From Date",
"To Date",
"Promo Days",
Category,
Chain,
"Chain (Group)",
"Media Type",
Brand,
"Special Type",
Company,
Description,
"Price Offer",
"Price Offer Type",
"Volume Offer",
"Volume Offer Type",
"Product Offer Type",
Offer,
"Offer Percentage",
"Final Price",
"First Price",
"Unit Size",
"Entry Size"
FROM [lib://Sales_Uploads/QVD/Pan.qvd]
(qvd);

Inner Join IntervalMatch(Date)
load StartingWeek,EndingWeek     // i assume you have used "From Date" / "To Date" instead of weekdates
Resident Pan;