How conditional join script should be to store inflated data in qvd ?

[prahlad_infy]

Hi All ,

I have a unique list of Zone with four distinct Values 

Unique Zone List

NORTH

SOUTH

EAST

WEST

I want this to be conditionally joined to the source data in such a way that if for given company  a particular Zone does not exist  then it should be added to source data with measure (Profit) as 0 and stored in qvd .

Data :

LOAD * INLINE [
Company, Zone, Profit
A, NORTH, 200
A, SOUTH, 100
B, EAST, 50
B, SOUTH, 75
C, NORTH, 20
C, SOUTH, 40
C, EAST, 20
C, WEST, 30
D, EAST, 70

];

Thank you .

[prahlad_infy]

@tresesco  @sunny_talwar  ...

How to do conditional join , where i intentionally want data to inflate ..  

[sunny_talwar]

Here is one option

Data:
LOAD * INLINE [
    Company, Zone, Profit
    A, NORTH, 200
    A, SOUTH, 100
    B, EAST, 50
    B, SOUTH, 75
    C, NORTH, 20
    C, SOUTH, 40
    C, EAST, 20
    C, WEST, 30
    D, EAST, 70
];

Concatenate(Data)
LOAD Company,
	 Zone,
	 Profit
Where Len(Trim(Zone)) > 0;
LOAD Company,
	 If(SubStringCount(Zone_List, SubField('NORTH,SOUTH,EAST,WEST', ',', IterNo())) = 1, Null(), SubField('NORTH,SOUTH,EAST,WEST', ',', IterNo())) as Zone,
	 0 as Profit
While IterNo() <= 4;
LOAD Company,
	 Concat(DISTINCT Zone, ',') as Zone_List,
	 4-Count(DISTINCT Zone) as Count
Resident Data
Group By Company;

[prahlad_infy]

Thank You Sunny . It worked , but actually in real data Zone_List consist of many distinct values , and this approach requires hard coding  .

Is there any way alternate to avoid this ? I tried with Cartesian join but due to limited expertisee not able to get desired result . 

 

[sunny_talwar · Accepted solution]

Without hard-coding

Data:
LOAD * INLINE [
    Company, Zone, Profit
    A, NORTH, 200
    A, SOUTH, 100
    B, EAST, 50
    B, SOUTH, 75
    C, NORTH, 20
    C, SOUTH, 40
    C, EAST, 20
    C, WEST, 30
    D, EAST, 70
];

AllZones:
LOAD Concat(DISTINCT Zone, ',') as AllZones
Resident Data;

LET vAllZones = Peek('AllZones');
DROP Table AllZones;

Concatenate(Data)
LOAD Company,
	 Zone,
	 Profit
Where Len(Trim(Zone)) > 0;
LOAD Company,
	 If(SubStringCount(Zone_List, SubField('$(vAllZones)', ',', IterNo())) = 1, Null(), SubField('$(vAllZones)', ',', IterNo())) as Zone,
	 0 as Profit
While IterNo() <= FieldValueCount('Zone');
LOAD Company,
	 Concat(DISTINCT Zone, ',') as Zone_List,
	 4-Count(DISTINCT Zone) as Count
Resident Data
Group By Company;