Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ashis
Creator III
Creator 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)
  • johnw

2 Solutions

Accepted Solutions
trdandamudi
Master II
Master II

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

Vegar
MVP
MVP

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
;

  

View solution in original post

5 Replies
trdandamudi
Master II
Master II

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

 

Vegar
MVP
MVP

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
;

  

ashis
Creator III
Creator III
Author

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

ashis
Creator III
Creator III
Author

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 ?

Vegar
MVP
MVP

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