Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Load script table and calculate field using another table

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;                             

example.png

1 Solution

Accepted Solutions
Contributor III
Contributor III

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

View solution in original post

5 Replies
Contributor
Contributor

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

I mean based on which field.

Contributor III
Contributor III

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.


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Contributor III
Contributor III

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

AllData.PNG

Intervals (PS: This table will have data that is not present in AllData)

Intervals.PNG

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;

Contributor III
Contributor III

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

View solution in original post