topic Re: Data Transformation from SpreadSheet in QlikView

Data Transformation from SpreadSheet

Wed, 20 Jan 2016 12:26:29 GMT

Dear QV Experts,

I am strucked here to separate the Year & Month [F1] column from the input spreadsheet.

Both the Input & QVW attached.

Please suggest me on this.

Thanks

petter — Wed, 20 Jan 2016 12:44:53 GMT

If you modify the first part of your load script like this:

You can have a Table Box like this:

jonas_rezende — Wed, 20 Jan 2016 12:45:55 GMT

Hi, Sasi Kumar.

Utilize the functions left() and right(). Example:

P.S.: you can even use a logic to handle the month without the year.

Hope this helps!

Wed, 20 Jan 2016 15:52:34 GMT

Thanks Peter. It is working. But one change would be required that

First Row Dec2012 should not be considered.

In the data above, Jan-Dec should be mapped to Year2013. The year is in the lastrow.

Can you please suggest me.

Thanks

petter — Wed, 20 Jan 2016 16:45:23 GMT

One way of getting the year correct is to modify the load to this:

CPINDEX:

LOAD

F1,

If( Len(F1) = 3 AND Len(Peek(F1))>3 , Peek(Year)+1 , If( RecNo() = 1 , Right(F1,4)+0 , Peek(Year) ) ) AS Year,

Month( Date#( '1/' & Left(F1,3) & ' 2000','D/MMM YYYY')) AS Month,

CPI,

[CPIH 2,3],

[RPI 4],

RPIJ

FROM

[CosIndex.xlsx]

(ooxml, embedded labels, table is Sheet1, filters(....

.....

Wed, 20 Jan 2016 18:30:26 GMT

Excellent. It works for me now. Thanks a lot.