CASE or IF Statement and then where to put it

[Lisa2]

Hi, I need to make a new column called AllBins.  I have loaded in an Excel table with item numbers and bin strings.  I have  truncated bin letters off of bin strings in the LOAD table.  For every item number, I need to combine its bin letter(s) in a new column called AllBins.  See attachment.  I'm not sure if this needs a CASE statement or an IF statement.  Should it go in the LOAD statement or make a temp table?  If someone could point me in the right direction.  Thanks!

[Arthur_Fong]

There you go:

If statement and concat function works well in this case.

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

[Lisa_P]

Try this:

Bins:
LOAD ItemNumber,
BinString,
left(BinString,1) as Bin
FROM
[C:\MyFolder\Bins.xlsx]
(ooxml, embedded labels, table is Bins);

Bins2:
Left Join(Bins)
Load ItemNumber,
If(AllBins='A&B&C', 'ALL', AllBins) as AllBins;
Load ItemNumber,
Concat(Bin, '&') as AllBins
Resident Bins
Group by ItemNumber;

Best practice is to do this in the load script.

[Lisa2]

Almost there....

It works but It's adding a letter for every time the item number is repeated with the same letter, so it comes out like  A&A&A&A&B&B&B&B&B&B&B&B&B&B&B&B&B&B&C&C&C.  I've revised my example to reflect this.

[Arthur_Fong · Accepted solution]

Try this:

Raw:
load distinct *,left(BinString,1) as Bin ,1 as BinCount inline [
ItemNumber,BinString
424,C25
424,A56
424,C25
424,C25
424,A56
424,A56
424,B89
759,C36
759,B12
759,B12
759,C36
759,C36
759,B12
759,C36
759,B12
254,A23
254,B45
254,A23
896,A78
896,A78
];

left join(Raw)
Data:
load ItemNumber,
Sum(BinCount)as BinCount2
resident Raw
Group by ItemNumber;

tempData:
load max(BinCount2) as maxCount
Resident Raw;
let vMax =peek('maxCount',0,'tempData');

drop table tempData;

left join(Raw)
Data:
load ItemNumber,
concat(distinct Bin,'&')
as BinConcat
resident Raw
group by ItemNumber;

Data:
load *,if(BinCount2=$(vMax),'All',BinConcat) as AllBin
resident Raw;
drop table Raw;

exit SCRIPT;

[Lisa2]

I'm not quite following the first part.  What does this mean:

1 as BinCount

And how do I incorporate that first part with my Excel file.  I also have other columns in my Excel table that I need.  Is this Raw table supposed to be joined to the original Bin table (with all the other columns that are there)?

Let me know, thanks!

[Arthur_Fong]

This Raw table is the original Bin table.

I use inline table just as an example.

In your case, instead of doing inline, replace the Raw table with your excel table. 

[Brett_Bleess]

Lisa, I am guessing you got everything working given no further comments, if that is the case, do not forget to come back to the thread and mark Arthur's post using the Accept as Solution button that helped you get things working as you wanted.  You can mark more than one, but I think one of the last ones is likely the best one to mark.  If you do still need help, leave an update.

Regards,
Brett