Left join with different granularity of data

Anonymous — Wed, 15 Nov 2017 16:32:09 GMT

I have a requirement to populate a new column in Alert table as ‘Alert Status ‘ by checking into Ticket tables which I have TicketwithL3Node and TicketwithL4Node both the tables have the same number (Name) of columns just don’t have value for ‘L4Node’ in TicketwithL3Node.

So if I do left join with Alert table and TicketwithL3Node it should be with Key L3Node

And if I do left join with Alert table and TicketwithL4Nodeit should be with Key L3Node&’|’& L4Node as Key

And finally

if TicketType =’Performace’ then Alert Status should populate as ‘Ticket raised’

if TicketType =Comment then Alert Status should populate as ‘No Action Required’

if TicketType =Null() then Alert Status should populate as ‘No Action taken’

Tried- after doing left join separately for both the tables with Alert , concatenated the tables but it is not giving correct count of alert because of concatenation. ( Can’t Do distinct count as Alert number can be multiple)

Please advise if somebody worked with same type of problem.

Please find sample data.

Let me know if you need any more information.

Any help will be appreciated.

Thanks,

BKC

Re: Left join with different granularity of data

Anonymous — Thu, 16 Nov 2017 16:20:51 GMT

Resolved by using the combination of inner join , concatenation and where exists.

Below is the sample code.

Alert:

LOAD Alert,

L3,

//L3&'|'& L4 as Key,

L3 as AlertL3,

L4 as AlertL4

FROM

[L3andL4TestData.xlsx]

(ooxml, embedded labels, table is Sheet1);

inner Join(Alert)

Ticket:

LOAD Ticket,

L3,

L4,

'L3' as Flag

FROM

[L3andL4TestData.xlsx]

(ooxml, embedded labels, table is Sheet2);

store Alert into $(vqvdpath)\1.Extract\Alert.qvd (qvd);

drop Table Alert;

Alert1:

LOAD Alert,

L3&'|'& L4 as Key,

L3 as AlertL3,

L4 as AlertL4

FROM

[\L3andL4TestData.xlsx]

(ooxml, embedded labels, table is Sheet1);

inner Join(Alert1)

LOAD Ticket,

L3&'|'& L4 as Key,

L3 ,

L4,

'L4' as Flag

FROM

[\L3andL4TestData.xlsx]

(ooxml, embedded labels, table is Sheet3);

store Alert1 into $(vqvdpath)\Alert1.qvd (qvd);

drop table Alert1;

AlerFinal:

LOAD L3,

Alert,

AlertL3,

AlertL4,

Ticket,

L4,

Flag

FROM

[$(vqvdpath)\Alert.qvd]

(qvd);

Concatenate

LOAD Key,

Alert,

AlertL3,

AlertL4,

Ticket,

L3,

L4,

Flag

FROM

[$(vqvdpath)\Alert1.qvd]

(qvd);

Concatenate (AlerFinal)

Alert2:

LOAD Alert,

L3,

L3 as AlertL3,

L4 as AlertL4

FROM

[\L3andL4TestData.xlsx]

(ooxml, embedded labels, table is Sheet1) where not Exists (L3);

// Composite key can be used in where exists

Thanks,

BKC

topic Left join with different granularity of data in QlikView

Left join with different granularity of data

Re: Left join with different granularity of data