join Help

[bhavvibudagam]

Hi Experts,

Can any One please help me on below requirement.

Config Table is the Main Table.

PG,Capabily and Coverage are the Subtables.

• In between Config and PG the common Field is PG.Based on PG need to do left join.
• In between Config and Capability common Field is Capability.Based on Capability need to do left join.
• In between Config and Coverage common Field is Coverage.Based on Coverage need to do left join.

when PG is not null hours need to join to the PG, When PG and Capability is null then Hours need to join to the Coverage. When  PG and Coverage is null Hours need to join to the Capability.

The issue is Here only first left join is working.

Please find the below code

Please help me am i missed anything.

Config:

LOAD ID,

     PG,

     Capability,

     Coverage

FROM

(ooxml, embedded labels, table is Sheet1);

left join(Config)

PG:

LOAD

//[Financial Year],

     PG,

     Hours

FROM

(ooxml, embedded labels, table is Sheet1);

left join(Config)

Config:

LOAD

//[Financial Year],

     Coverage,

     Hours

FROM

(ooxml, embedded labels, table is Sheet1);

left join(Config)

LOAD

//[Financial Year],

     Capability,

     Hours

FROM

(ooxml, embedded labels, table is Sheet1);

[Anil_Babu_Samineni]

You don't have any common field(Primary Key) in your tables. This is the case, I would think to generate primary key using all set up common fields with Key generate using Autonumber function

So, That deserve like this how to create composite key

[devarasu07]

Hi,

what is your expected output?

may be u can try like below method using applymap

PGMap:
mapping LOAD [Financial Year] & '|'& PG as PGKey,
    Hours
FROM 
(ooxml, embedded labels, table is Sheet1);


CapabilityMap:
Mapping LOAD [Financial Year] & '|'& Capability as CapabilityKey,
    Hours
FROM 
(ooxml, embedded labels, table is Sheet1);


CoverageMap:
Mapping LOAD [Financial Year] & '|'& Coverage as CoverageKey,
    Hours
FROM 
(ooxml, embedded labels, table is Sheet1);


Fact:
load *,ApplyMap('PGMap',PGKey,'NA') as PG_Hours,
ApplyMap('CapabilityMap',CapabilityKey,'NA') as Capability_Hours,
ApplyMap('CoverageMap',CoverageKey,'NA') as Coverage_Hours;
load *,
[Financial Year] &'|'& PG as PGKey,
[Financial Year] &'|'& Capability as CapabilityKey,
[Financial Year] &'|'& Coverage as  CoverageKey;
LOAD ID,
    PG,
    Capability,
    Coverage,
      '2018' as [Financial Year]
FROM

(ooxml, embedded labels, table is Sheet1);