Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need some help here.
I have 1 table and here are some of the elements
Transaction Date
P_KEY
etc
I want to use the Transaction Date and the P_KEY to find a value in another table that looks like this
P_KEY
Program
Program Start Date
Program End Date
My Final table needs to look like this
Transaction Date
P_KEY
Program
Program Start Date
Program End Date
Thanks in advance
SET DateFormat='MM/DD/YYYY';
Transaction:
load * inline [
Transaction_Date, P_KEY
08/01/2016 , 1
08/01/2016 , 2
08/02/2016 , 1
08/02/2016 , 2
08/03/2016 , 1
08/03/2016 , 2
08/31/2016 , 1
];
Program:
load * inline [
P_KEY , Program , Program_Start , Program_End
1 , A, 07/20/2016 , 08/01/2016
1 , B , 08/02/2016 , 08/31/2016
2 , A , 08/01/2016 , 08/31/2016
];
Inner Join
IntervalMatch (Transaction_Date, P_KEY)
Load distinct Program_Start, Program_End, P_KEY resident Program;
a best pratic its use apply map
Map_Td:
maping load
P_KEY,
Transaction Date
from ....
table:
load
P_KEY
Program
Program Start Date
Program End Date
applymap('Map_Td',P_KEY,'not found') as Transaction Date
from ...
Sounds like you wanted to JOIN your tables.
Sorry that I forgot to post sample data!
Transaction Table
Transaction_Date P_KEY
08/01/2016 1
08/01/2016 2
08/02/2016 1
08/02/2016 2
08/03/2016 1
08/03/2016 2
...
08/31/2016 1
Program Table
P_KEY Program Program_Start Program_End
1 A 07/20/2016 08/01/2016
1 B 08/02/2016 08/31/2016
2 A 08/01/2016 08/31/2016
Final Table
Transaction_Date P_KEY Program Program_Start Program_End
08/01/2016 1 A 07/20/2016 08/01/2016
08/01/2016 2 A 08/01/2016 08/31/2016
08/02/2016 1 B 08/02/2016 08/31/2016
08/02/2016 2 A 08/01/2016 08/31/2016
08/03/2016 1 B 08/02/2016 08/31/2016
08/03/2016 2 A 08/01/2016 08/31/2016
...
08/31/2016 1 B 08/02/2016 08/31/2016
I need to see where the transaction date falls in between the program start and program end date for a give P_KEY
your second table does not have Transaction Date and only P_KEY is common between both the tables.
To get the respective results from second tables based on P_KEY and Transaction Date you may need to have the same fields or fields carrying matching records in the second table.
Once you find them you can JOIN them by selecting a JOIN type (outer, inner, left or right) based on your requirement.
Can you please post sample Data
Some Corrections, Tell me if i am wrong
First:
Mapping Load
Transaction Date
P_KEY
etc
Second:
P_KEY,
Applymap('First',Trim([Transaction Date])) as [Transaction Date]
Program
Program Start Date
Program End Date
you can check interval match in QlikView.
Please take a look at the attached solution.
SET DateFormat='MM/DD/YYYY';
Transaction:
load * inline [
Transaction_Date, P_KEY
08/01/2016 , 1
08/01/2016 , 2
08/02/2016 , 1
08/02/2016 , 2
08/03/2016 , 1
08/03/2016 , 2
08/31/2016 , 1
];
Program:
load * inline [
P_KEY , Program , Program_Start , Program_End
1 , A, 07/20/2016 , 08/01/2016
1 , B , 08/02/2016 , 08/31/2016
2 , A , 08/01/2016 , 08/31/2016
];
Inner Join
IntervalMatch (Transaction_Date, P_KEY)
Load distinct Program_Start, Program_End, P_KEY resident Program;