Linking two tables

[FionnM]

I'm pretty new to scripting so I apologise if this is a really obvious question.

I have two tables, one called "Events" and one called "Inflation". The "Events" table contains numerous fields like "Event Description", "Start Date", "End Date" and so on. The "Inflation" table just has two fields, "Inflation Month-Year" (e.g. "Sep-2021") and "Inflation Rate" (the rate of inflation in that month, relative to a baseline).

I'm trying to figure out a quick way to associate the month that a given event ended with the rate of inflation in that month. In my script for the Events table, I've entered a line like this:

date([Event End Date], 'MMM-YYYY') as [Inflation Month-Year]

 which creates a key associating the "Events" table with the "Inflation" table.

From there, I'm stuck, and can't figure out how to then associate the Inflation Rate field with the corresponding month in the "Events" table.

[marcus_sommer]

Your Month-Year isn't a Month-Year else a date because the date() just format values but didn't change their value.

Independent from this you couldn't simply associate this period-information with start/end range else start/end needs to resolved to a real date before they could be matched. The logic is here very well explained: IntervalMatch - Qlik Community - 1464547 whereby personally I prefer internal while-loops to get the same end-results.

Beside this I wouldn't tend to associate these data with separate tables else merging them within a single table. This may look like:

m: mapping load date(date#("Inflation Month-Year", 'MMM-YYYY')), "Inflation Rate"
from "Inflation";

Events:
load *, applymap('m', date(monthstart(Date), 'no match') as "Inflation Rate";
load *, date("Start Date" + iterno() - 1) as Date
from Events
while date("Start Date" + iterno() - 1) <= "End Date";

- Marcus

[FionnM]

Hi Marcus,

Thanks for your suggestion. I think merging the two tables would be ideal but I'm not sure how to go about doing it.

The Inflation table imports data from an Excel spreadsheet with three columns (fields): Year, Month (in plain text) and VALUE. Should I create two additional columns called "Period Start Date" and "Period End Date"?

[marcus_sommer · Accepted solution]

The inflation data don't need a start- and end-date if there is just one value per Year & Month and the above could be used to merge them - quite a small adjustment:

m: mapping load date(date#("Month" & '-' & "Year", 'MMM-YYYY')), "Inflation Rate"
from "Inflation";

Events:
load *, applymap('m', date(monthstart(Date), 'no match') as "Inflation Rate";
load *, date("Start Date" + iterno() - 1) as Date
from Events
while date("Start Date" + iterno() - 1) <= "End Date";

By converting/interpreting month-names or weekdays it's important that the interpretation-variable at the beginning of the script fits to the loaded data - if not they need to adjusted in this way.

- Marcus

[FionnM]

Thanks Marcus.

I modified your script slightly:

m: mapping load date(date#("Month" & '-' & "Year", 'MMM-YYYY')), [Inflation rate]
Resident Inflation;

Events:
load *, applymap('m', date(monthstart(Date)), 'no match') as "Inflation Rate";
load *, date("Start Date" + iterno() - 1) as Date
from Events
while date("Start Date" + iterno() - 1) <= "Event End Date";

Now it's only throwing this error:

Any ideas? Thanks for all your help.

[marcus_sommer]

The from-part is missing a proper path + file-format statement and tries to interpret the while-loop for it. This means are the Events already loaded replace the from with resident and otherwise apply the path + file-format information.

- Marcus

[FionnM]

Hi Marcus,

I apologise if there's something really obvious I'm missing here. I replaced "from" with "Resident" and now it's throwing this error:

[FionnM]

I don't know if this is relevant but the fields in the Events table are called "Event Start Date" and "Event End Date".

[marcus_sommer]

My example was just to demonstrate the logic. If the existing field- and table-names are different to it - it needs to be adjusted to them.

- Marcus

[FionnM]

I've fiddled around with it a bit and now it's doing what I want it to do. Thanks so much for all your help!