Data Transformation from SpreadSheet

[#1]

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]

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

You can have a Table Box like this:

[jonas_rezende]

Hi, Sasi Kumar.

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

• left(F1,3) as Month
• right(F1,4) as Year

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

Hope this helps!

[#4]

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

First Row Dec2012 should not be considered.

Jan	124.4
Feb	125.2
Mar	125.6
Apr	125.9
May	126.1
Jun	125.9
Jul	125.8
Aug	126.4
Sep	126.8
Oct	126.9
Nov	127.0
Dec 2013	127.5

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

Can you please suggest me.

Thanks

[petter · Accepted solution]

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(....

.....

[#6]

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