Month Field cration

[NavinReddy]

Dear Experts,

I have data like jan 2015,Feb 2015......Dec 2015

in Jan 2015 inside i have four Fields( Jan Cost (in USD),Jan Units  (in FTE),Jan Units (in HRS)    ,Jan Res Availability (in HRS))
Here how can i create Month field using cross table
is there any way to create Month1,Month2,Month3,Month4
Please share your valuable suggestions
Please find the attachment

Thanks in Advance
Niranjan

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

tabProgPlan:
CrossTable(ColName, Data, 6)
LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/682464-142274/Sample%20Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(Remove(Col, Pos(Top, 56)),Remove(Col, Pos(Top, 55))));

Left Join (tabProgPlan)
LOAD Distinct
  ColName,
  Month(Date#(SubField(ColName,' ',1),'MMM')) as Month,
  Mid(ColName, Index(ColName, ' ')+1) as Type
Resident tabProgPlan;

Join
First 1
LOAD SubField(@1,' ',2) as Year
FROM
[http://community.qlik.com/servlet/JiveServlet/download/682464-142274/Sample%20Data.xlsx]
(ooxml, no labels, table is Sheet1, filters(Remove(Col, Pos(Top, 6)),Remove(Col, Pos(Top, 5)),Remove(Col, Pos(Top, 4)),Remove(Col, Pos(Top, 3)),Remove(Col, Pos(Top, 2)),Remove(Col, Pos(Top, 1))));

Left Join (tabProgPlan)
LOAD Distinct
  Year,
  Month,
  MonthName(MakeDate(Year, Month)) as MonthName
Resident tabProgPlan;

hope this helps

regards

Marco

[NavinReddy]

Hi Marco,

Thank you so much really its great help to me

Best Regards,

Niranjan

[MarcoWedel]

You're welcome

regards

Marco