Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am now stuck with a data load problem. I am trying to load a column of data that is separated using a comma. Is it possible?
Here is the sample data -
Name | Sports |
Joe | Baseball, Football |
Bill | Bowling, Football, Baseball |
Fred | Tennis, Golf |
Alex | Golf, Football |
So I want to load sports of different people and be able to visualize for example how many people like baseball?
thanks for any help.
So I did find a solution for this, during the load statement -
LOAD Name, if(index(Sports,',')=0,Null(),SubField(Sports,',',1))as Sports_1, if(index(Sports,',')=0,Null(),SubField(Sports,',',2))as Sports_2, if(index(Sports,',')=0,Null(),SubField(Sports,',',3))as Sports_3, if(index(Sports,',')=0,Null(),SubField(Sports,',',4))as Sports_4 FROM 'lib://Scratch/11111sports.xlsx' (ooxml, embedded labels, table is Sheet1);
So I did find a solution for this, during the load statement -
LOAD Name, if(index(Sports,',')=0,Null(),SubField(Sports,',',1))as Sports_1, if(index(Sports,',')=0,Null(),SubField(Sports,',',2))as Sports_2, if(index(Sports,',')=0,Null(),SubField(Sports,',',3))as Sports_3, if(index(Sports,',')=0,Null(),SubField(Sports,',',4))as Sports_4 FROM 'lib://Scratch/11111sports.xlsx' (ooxml, embedded labels, table is Sheet1);
It is much easier (and cleaner) if you omit the third parameter of SubField():
Load
Name,
Trim(SubField(Sports,',')) as Sport
From ...
HIC