Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ashissau
Contributor III

Need joining logic

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.

Labels (1)
2 Solutions

Accepted Solutions
trdandamudi
Honored Contributor II

Re: Need joining logic

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
];

Logic.PNG

 

View solution in original post

Partner
Partner

Re: Need joining logic

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
;

  

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

5 Replies
trdandamudi
Honored Contributor II

Re: Need joining logic

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
];

Logic.PNG

 

View solution in original post

Partner
Partner

Re: Need joining logic

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
;

  

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

ashissau
Contributor III

Re: Need joining logic

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

ashissau
Contributor III

Re: Need joining logic

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 ?

Partner
Partner

Re: Need joining logic

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
Plees ekskuse my Swenglish and or Norweglish spelling misstakes