Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I've tried to find something similar in the help but could not get something to help me.
I'm trying to generate data in a table by doing a lookup in another temp table to see who was the responsible person at that date.
Please see below the the code I have currently. Line 18 is where I think things should happen (maybe with FirstSortedValue?)
Table2:
LOAD [Policy],
[From Date],
[Responsible person]
FROM
(ooxml, embedded labels);
Table1:
LOAD [Date] as [Transaction date],
Client,
[Policy #],
[Commission (incl. VAT)] as Commission,
Amount,
????? as [Person]
FROM
(ooxml, embedded labels);
DROP TABLE Table2;
I noticed there was now two columns for the Policy# in the table: [Policy] & [Policy Num]
Line 67 and down then suddenly made sense to me and I tweaked it by replacing the rest of the code with the code below.
IntervalMatch(Date, [Policy])
Left Join (AllData)
LOAD [From Date], [To Date], [Policy Num] as [Policy]
Resident Intervals;
Left Join (AllData)
LOAD [Responsible Partner], [From Date], [To Date], [Policy Num] as [Policy]
Resident Intervals;
Drop Table Intervals;
Basically the load of the INTERVALS table added a new field called Policy Num where I should have renamed the field to match the ALLDATA table.
The second load was just to add the responsible partner to the ALLDATA table - I can probably drop the [To Date] and I will still get the same result. Let me know if you think there is a quicker way (ALLDATA will grow by about 50k items a month).
Thanks for helping @Jonathan Dienst
Can you please explain how are you expecting the Person from Table 2 to be mapped to Table1?
I mean based on which field.
Example for first line in Table 1:
Step 1: Find all the items in Table 2 with Policy = 11
Step 2: Sort these items according to "From Date" (Newest to Oldest)
Step 3: Return the "Responsible Person" of the first record in in Step 2 with a "From Date" < "Date"
This looks like a slowly changing dimension, for which I would use an interval match
First load the lookup table
T_Table2:
LOAD [Policy],
[From Date],
[Responsible person]
FROM
(ooxml, embedded labels);
Add a To Date for the interval:
Intervals:
LOAD [Policy],
If(Policy = Previous(Policy),
Previous([From Date]),
Today()) as [To Date],
[From Date],
[Responsible person]
Resident T_Table2
Order by Policy, [From Date] DESC;
DROP Table T_Table2;
Load the main data table:
Table1:
LOAD [Date] as [Transaction date],
Client,
[Policy #],
[Commission (incl. VAT)] as Commission,
Amount
FROM
(ooxml, embedded labels);
Now do the interval matching (respecting the Policy Number):
IntervalMatch(Date, Policy)
Left Join (Table1)
LOAD [From Date], [To Date], Policy
Resident Intervals;
Finally, bring the [Responsible person] into the main table (not strictly necessary):
// optional
Left Join (Table1)
LOAD *
Resident Intervals;
Drop Table Intervals;
// optional
Now [Responsible person] will appear as a field in Table1.
Thanks for the help - I think we're getting closer.
If I load the script as it is now (see at the bottom), the AllData table now have 16x3 lines where it should only have 3 lines.
The targeted end results should only have the original AllData table, with the corresponding Responsible Partner next to it.
If I remove the code from the IntervalMatch line 67 down it looks like the two tables are loading correctly:
AllData
Intervals (PS: This table will have data that is not present in AllData)
Script:
//First load the lookup table
T_Table2:
LOAD [Policy Num],
[Valid From] as [From Date],
[Responsible Partner]
FROM 'xxx\list2.xlsx'
(ooxml, embedded labels);
//Add a To Date for the interval:
Intervals:
LOAD [Policy Num],
If([Policy Num] = Previous([Policy Num]),
Previous([From Date]),
Today()) as [To Date],
[From Date],
[Responsible Partner]
Resident T_Table2
Order by [Policy Num], [From Date] DESC;
DROP Table T_Table2;
let path_Alles = 'xxx\*.xlsx';
for each File in filelist (path_Alles)
FindHeaderTemp:
LOAD RecNo() as DataStart
FROM $(File)
(ooxml, no labels)
WHERE A = 'Policy Number';
LET vHeaderSize = peek('DataStart') - 1;
DROP TABLE FindHeaderTemp;
AllData:
LOAD [Policy Number] as Policy,
Client,
Fees,
[Commission (incl. VAT)] as Commission,
Premium,
'Provider X' as Provider,
Date(Date#(SubField(SubField(SubField('$(File)','.',-2),'\',-1),' - ',1), 'YYYYMMDD')) as Date,
Date(Date#(SubField(SubField(SubField('$(File)','.',-2),'\',-1),' - ',1), 'YYYYMMDD'),'YYYYMM') as Period,
SubField(SubField(SubField('$(File)','.',-2),'\',-1),' - ',2) as [PMT Bank],
SubField(SubField(SubField(SubField('$(File)','.',-2),'\',-1),' - ',3),'&',1) as [PMT Ref 1],
SubField(SubField(SubField(SubField('$(File)','.',-2),'\',-1),' - ',3),'&',2) as [PMT Ref 2]
FROM
$(File)
(ooxml, embedded labels, header is $(vHeaderSize) lines)
Where Len(Trim([Policy Number])) > 2;
next File
IntervalMatch(Date, [Policy])
Left Join (AllData)
LOAD [From Date], [To Date], [Policy Num]
Resident Intervals;
Left Join (AllData)
LOAD *
Resident Intervals;
Drop Table Intervals;
I noticed there was now two columns for the Policy# in the table: [Policy] & [Policy Num]
Line 67 and down then suddenly made sense to me and I tweaked it by replacing the rest of the code with the code below.
IntervalMatch(Date, [Policy])
Left Join (AllData)
LOAD [From Date], [To Date], [Policy Num] as [Policy]
Resident Intervals;
Left Join (AllData)
LOAD [Responsible Partner], [From Date], [To Date], [Policy Num] as [Policy]
Resident Intervals;
Drop Table Intervals;
Basically the load of the INTERVALS table added a new field called Policy Num where I should have renamed the field to match the ALLDATA table.
The second load was just to add the responsible partner to the ALLDATA table - I can probably drop the [To Date] and I will still get the same result. Let me know if you think there is a quicker way (ALLDATA will grow by about 50k items a month).
Thanks for helping @Jonathan Dienst