Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Currently I have excel in following format (see attachment)
Month_ and regions in same column.
I'm trying to separate them in two different columns while keeping the association
like
RegionColumn Months
California Month_201107
California Month_201108
Desert Mountain Month_201107
Desert Mountain Month_201108
and so on.
I'm not able to achieve it , Any help would be appreciated
Try this:
Temp:
LOAD
IF(IsNum(SubField(A,'_',2))=0,A,Peek('Region')) AS Region
,IF(IsNum(SubField(A,'_',2)),SubField(A,'_',2),0) AS Month
FROM
(ooxml, no labels, table is Sheet1)
WHERE NOT IsNull(A);
Data:
NoConcatenate
LOAD
*
RESIDENT Temp
WHERE Month <> 0;
DROP TABLE Temp;
Hope this helps,
Jason
Try this:
Temp:
LOAD
IF(IsNum(SubField(A,'_',2))=0,A,Peek('Region')) AS Region
,IF(IsNum(SubField(A,'_',2)),SubField(A,'_',2),0) AS Month
FROM
(ooxml, no labels, table is Sheet1)
WHERE NOT IsNull(A);
Data:
NoConcatenate
LOAD
*
RESIDENT Temp
WHERE Month <> 0;
DROP TABLE Temp;
Hope this helps,
Jason
Thank you Jason