topic Re: Loading single column Excel in Qlikview in QlikView

Loading single column Excel in Qlikview

onetwothree123 — Tue, 07 Aug 2012 21:19:45 GMT

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

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 — Tue, 07 Aug 2012 23:05:55 GMT

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 — Wed, 08 Aug 2012 16:41:53 GMT

Thank you Jason