topic Re: How conditional join script should be to store inflated data in qvd ? in QlikView

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

prahlad_infy — Mon, 16 Dec 2019 17:54:29 GMT

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 .

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

prahlad_infy — Tue, 17 Dec 2019 09:37:39 GMT

@tresB @sunny_talwar ...

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

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

sunny_talwar — Tue, 17 Dec 2019 12:21:35 GMT

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;

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

prahlad_infy — Tue, 17 Dec 2019 13:27:00 GMT

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 .

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

sunny_talwar — Tue, 17 Dec 2019 13:33:31 GMT

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;