Load script table and calculate field using another table

[pjn123pjn123]

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;                              

[Anonymous]

Can you please explain how are you expecting the Person from Table 2 to be mapped to Table1?

I mean based on which field.

[pjn123pjn123]

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"

[jonathandienst]

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.

[pjn123pjn123]

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;

[pjn123pjn123 · Accepted solution]

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