topic Need joining logic in QlikView

Need joining logic

ashis — Sat, 16 Nov 2024 03:04:24 GMT

Hi ,

I have a excel file, it has 5 colums

BusinessLevel1, BusinessLevel2, BusinessLevel3,BusinessLevel4,BusinessLevel5 . 5 being the most granularity of data.

BusinessLevel1, BusinessLevel2, BusinessLevel3,BusinessLevel4,BusinessLevel5

xxx,yyy,zzz,ppp,qqq

abc,bbb,ccc, , ,

ddd,eee, , ,rrr

I need to create a derive column name Business based on the logic , first look for data in BusinessLevel5, if found take it , if there is no data in level5 look for businesslevel4 , if data found take it if not look for leve3 and so forth in the script level.

Please help me to jot down the logic.

Re: Need joining logic

trdandamudi — Mon, 08 Jul 2019 12:37:17 GMT

One way you can do this is as below:

Data:
Load *,
If(Len(BusinessLevel5)>0,BusinessLevel5,
If(Len(BusinessLevel4)>0,BusinessLevel4,
If(Len(BusinessLevel3)>0,BusinessLevel3,
If(Len(BusinessLevel2)>0,BusinessLevel2,
If(Len(BusinessLevel1)>0,BusinessLevel1))))) as Business;
Load * Inline [
BusinessLevel1, BusinessLevel2, BusinessLevel3, BusinessLevel4, BusinessLevel5
xxx,yyy,zzz,ppp,qqq
abc,bbb,ccc,,,
ddd,eee,,,rrr
];

Re: Need joining logic

Vegar — Mon, 08 Jul 2019 13:03:04 GMT

You could solve this with a series of nested IF statements like @trdandamudi example above.

You could also solve it by performing a crosstable load and picking the value of the highest BusinessLevelX like below. I added an dimension (ID) to the row in order to get the crosstable to work.

set NullInterpret =''; //needed in order to inteperet empty values in inline as null Businesslevel: CrossTable (Businesslevel, BusinessName) LOAD * inline [ ID, BusinessLevel1, BusinessLevel2, BusinessLevel3,BusinessLevel4,BusinessLevel5 A, xxx,yyy,zzz,ppp,qqq B, abc,bbb,ccc, , , C, ddd,eee, , ,rrr ]; RIGHT JOIN (Businesslevel) LOAD MaxString(Businesslevel) as Businesslevel, ID Resident Businesslevel group by ID ;

Re: Need joining logic

ashis — Tue, 09 Jul 2019 03:51:55 GMT

Hi trdandamudi, Your solution works . Thank you so much for your reply.

Re: Need joining logic

ashis — Tue, 09 Jul 2019 03:54:14 GMT

Hi Vegar, Thank you so much for your reply.

It suggestion works as well. At this moment we do not have ID column that you have introduced. Do i need to create on the fly in order to work the cross table ?

Re: Need joining logic

Vegar — Tue, 09 Jul 2019 04:55:03 GMT

You don't need an ID, but you do need at least one dimension that is not to
be transposed.

If you no not have a dimension you can create one like I did and drop it
after your Crosstable is executed.

-Vegar