Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 .
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;
@tresesco @sunny_talwar ...
How to do conditional join , where i intentionally want data to inflate ..
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;
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 .
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;