Need joining logic

[ashis]

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.

[trdandamudi · Accepted solution]

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

[Vegar · Accepted solution]

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]

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

[ashis]

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]

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