Linking two files to exclude duties from revenue

[jorgen_santberg]

Hi,

I am having an issue linking two different sources of data.

I have my salesdata in one table:

Table1:

LOAD

    Invoicedate,

     Monthstart(Invoicedate) as Time,

     ArtID,

     Revenue

and data regarding duties in another table:

Table2:

LOAD

     ArtID,

     Year,

     Duty

In my salesdata the revenue includes duties, but I needto see revenues without duties. The duties change over the years and differ per ArtID.

[Anil_Babu_Samineni]

May be this?

Table2:

LOAD

     ArtID,

     Year,

     Duty;

Table1:

LOAD

    Invoicedate,

     Monthstart(Invoicedate) as Time,

     ArtID,

     Revenue From SalesTable Where Not Exists (Duty, Revenue);

[Colin-Albert]

Use a mapping table to bring the Duty values into Table 1 with a key that combines the ArtID & year.

Duty_Map:

LOAD

     ArtID & '!' &  Year,

     Duty

from XXXX

Table1:

LOAD

    Invoicedate,

     Monthstart(Invoicedate) as Time,

     ArtID,

     applymap('Duty_Map, 'ArtID & '!' &  year(Invoicedate), 'N/A') as Duty,

     Revenue

from YYYY

[Colin-Albert]

Typo - the apply map line should be

applymap('Duty_Map', ArtID & '!' &  year(Invoicedate), 'N/A') as Duty,

[Colin-Albert · Accepted solution]

Use a mapping table to bring the Duty values into Table 1 with a key that combines the ArtID & year.

Oops - Mapping Load is missing!  Check when copying scripts  - Doh!

Duty_Map:

MAPPING LOAD

     ArtID & '|' &  Year,

     Duty

from XXXX

Table1:

LOAD

    Invoicedate,

     Monthstart(Invoicedate) as Time,

     ArtID,

     applymap('Duty_Map', ArtID & '|' &  year(Invoicedate), 'N/A') as Duty,

     Revenue

from YYYY

[jorgen_santberg]

It works, thanks!