joining with conditions

[ismailk94]

Hi all!

I'm attempting to translate some SQL syntax into Qlik script but I'm having trouble finding the solution.

I have two tables - "Fact_Transactions" and "Dim_Membership".

Fact_Transactions (Table1):

• "MemberID"
• "TransactionID"
• "TransactionDate"

Dim_Membership (Table2):

• "MemberID"
• "Subscription Start Date"
• "Subscription End Date"
• "ActualEndDate"

My objective is to move "ActualEndDate" to the "Fact_Transactions" table under the following conditions:

• "MemberID"(Table2)="MemberID"(Table1)
• "ActualEndDate"(Table2)>="TransactionDate"(Table1)
• "Subscription Start Date"(Table2) <= "TransactionDate"(Table1)
  
  Any ideas? 🙂
  

[Jure]

Hey,
use Intervalmatch function.
Br

[BawejaMedia]

Certainly! You can achieve this in Qlik script using the JOIN and WHERE clauses. Here's a sample script:

// Load Fact_Transactions table
Fact_Transactions:
LOAD
MemberID,
TransactionID,
TransactionDate
FROM [Path\to\Fact_Transactions.csv]
(txt, utf8, delimiter is ',', embedded labels);

// Load Dim_Membership table
Dim_Membership:
LOAD
MemberID,
[Subscription Start Date],
[Subscription End Date],
ActualEndDate
FROM [Path\to\Dim_Membership.csv]
(txt, utf8, delimiter is ',', embedded labels);

// Join Fact_Transactions with Dim_Membership based on MemberID
Transactions_With_EndDate:
LOAD
Fact_Transactions.*,
Dim_Membership.ActualEndDate as TransactionEndDate
RESIDENT Fact_Transactions
LEFT JOIN
Dim_Membership
ON
Fact_Transactions.MemberID = Dim_Membership.MemberID
AND
Dim_Membership.ActualEndDate >= Fact_Transactions.TransactionDate
AND
Dim_Membership.[Subscription Start Date] <= Fact_Transactions.TransactionDate;

// Drop temporary tables and fields
DROP TABLES Fact_Transactions, Dim_Membership;
DROP FIELDS MemberID, TransactionDate, [Subscription Start Date];

// Rename TransactionEndDate field
Transactions_With_EndDate:
RENAME FIELD TransactionEndDate TO ActualEndDate;

This script:

1. Loads the Fact_Transactions and Dim_Membership tables from their respective CSV files.
2. Joins the tables based on the MemberID field and the specified conditions using the LEFT JOIN clause.
3. Renames the TransactionEndDate field to ActualEndDate for clarity.

Make sure to replace "[Path\to\Fact_Transactions.csv]" and "[Path\to\Dim_Membership.csv]" with the actual file paths. Adjust field names and formatting as needed based on your Qlik environment.

[ismailk94]

Hi! Thanks for the feedback.

I am in QlikCloud.

the two tables are qvd-files, so I dont see how I can use the syntax you are proposing? 🙂

[ismailk94]

Hi Jure - thanks for the feedback. I looked at it as a possible way of getting around my issue but I don’t see how I can use the interval match and also include my three joins.

[JonnyPoole]

Definitely use IntervalMatch()

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes...

Its perfect for your setup.  It joins a record with a date from one table to record(s) that bear interval(s) in another table. 

In its most basic form table 1 has a Date and table 2 has start and end date.  The intervalmatch does a JOIN of each date in table1 to each matching interval in table2.

It also works when you need add another field to further restrict the matching of intervals. 

[marcus_sommer]

The suggested intervalmatch is definitely the right logic to get the wanted associations. But it could be also done with another and simpler approach, like this one (simplified):

m: mapping load ID & '|' & date(Start + iterno() - 1), F1 & '|' & F2
     from T2 while Start + iterno() - 1 <= End;

and in the final load you may use:

subfield(applymap('m', ID & '|' & Date, 'no match'), '|', 1) as F1

to get the first field (the subfield-stuff is not mandatory to a mapping but a simple way to return multiple fields like a join would would do without the need to create multiple mappings).

[ismailk94]

Hi Marcus!

This is interesting. I still have not managed to do the solution using intervalmatch but i am getting closer.

I would like to try your way but to do that here's a breakdown:

My Table 1 "FACT" looks like this
MemberID, TransactionDate, TransactionNumber

Table 2 - "DIM" looks like this:
MemberID, SubscriptionID, [Subscription Start Date], ActualEndDate, [Subscription Status]

Since a memberID can have several subscription-id's, my goal is to map the right transactions with the right subscriptions.
I have built it correctly in SQL syntax as mentioned above, but havent found a way to do it with Qlik-syntax.

What I want is to move all the fields from the DIM to the FACT and therefore I need to implement the rules that MemberID=MemberID AND ActualEndDate>=TransactionDate AND TransactionDate>=[Subscription Start Date] while doing the joins/applymaps.

If I try your way:
m: mapping load MemberID & '|' & date([TransactionDate] + iterno() - 1), [SubscriptionStartDate] & '|' & [ActualEndDate]
     RESIDENT DIM  while TransactionDate+ iterno() - 1 <= ActualEndDate;

I will get an error about TransactionDate not being part of the DIM-table...

[marcus_sommer]

The loop-resolving of the dates should be against the Start- and End-Date from the DIM and not the Transaction-Date from the facts. The last is used within the applymap() to build there the lookup-value against the mapping-table:

m: mapping load MemberID & '|' & date([SubscriptionStartDate] + iterno() - 1), 
                            [SubscriptionStartDate] & '|' & [ActualEndDate]
     RESIDENT DIM  while SubscriptionStartDate + iterno() - 1 <= ActualEndDate;

The general approach of using an internal while-loop to resolve a numeric range into single values as well as doing this with a mapping load will work - but in your described case of having multiple subscriptions per customer you could neither apply a mapping nor any join approach directly because it's not a 1:1 relationship else 1:n.

A mapping will take only the first matching and all others will be ignored and a join would create duplicate records. Therefore a solution will need additionally measurements or another approach.

Additionally measurements could mean to aggregate the subcription-id's and the various date-fields with concat() and using min/max on the date-fields to define the date-range and/or also counts + accumulations and/or offset-calculations between the multiple subscriptions. Many things are possible but it depends on the detailed requirements of the views what would be most suitable.

Personally I would tend not to join/merge this DIM else keeping it as dimension-table within the data-model with the above mentioned:

MemberID & '|' & date([SubscriptionStartDate] + iterno() - 1) as KEY 

as key between facts and dim and maybe applying afterwards a where exists() or an inner keep statement to the DIM to reduce the number of records.