Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
May be this?
Table2:
LOAD
ArtID,
Year,
Duty;
Table1:
LOAD
Invoicedate,
Monthstart(Invoicedate) as Time,
ArtID,
Revenue From SalesTable Where Not Exists (Duty, Revenue);
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
Typo - the apply map line should be
applymap('Duty_Map', ArtID & '!' & year(Invoicedate), 'N/A') as Duty,
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
It works, thanks!