Loading single column Excel in Qlikview

[onetwothree123]

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

[Jason_Michaelides · Accepted solution]

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

[onetwothree123]

Thank you Jason