Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

bhavvibudagam
Contributor II

join Help

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);

2 Replies

Re: Left join Help

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
devarasu07
Honored Contributor II

Re: Left join Help

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);

Capture.JPG