Decoding lot data into date YYYY-MM

[#1]

I have lot information coming out of the database and need to display the month of production in a column next to it. The data sometimes will have extra spaces preceding it which i know will need to be trimmed.  The first 2 characters are the two digit year and the 3rd character is the month of production as a letter, skipping "I" (A=JAN, B=FEB, C=MAR, D=APR, E=MAY, F=JUN, G=JUL, H=AUG, J=SEP, K=OCT, L=NOV, M=DEC) the rest of the code is not needed for what i am doing.  So how can I convert The first column to display what i have in the second column?

Lot Num         Month of Production

14G07            2014-07

12M18            2012-12

13A05             2013-01

[sunny_talwar]

Would you be able to share the portion of the script where you are bringing in the table and where you are doing this above manipulation:

LOAD *,
  Date(Date#('20' & Left(PSR_P2P3.TEXT.31, 2) & '-' &
  Pick(Match(Mid(PSR_P2P3.TEXT.31, 3, 1), 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M'), 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12), 'YYYY-MM'), 'YYYY-MM') as [Month of Production] ;

[#3]

I had that piece commented out... Now that I have that uncommented, It looks like the field is available for selection, however, the date isn't calculating.

LotMonth:

Load * Inline [

    PSR_P2P3.TEXT31, Month_of_Production

    ]

    ;

LOAD *,

  Date(Date#('20' & Left(PSR_P2P3.TEXT.31, 2) & '-' &

  Pick(Match(Mid(PSR_P2P3.TEXT.31, 3, 1), 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M'), 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12), 'YYYY-MM'), 'YYYY-MM') as Month_of_Production ;

[sunny_talwar]

Structure doesn't seem right to me. You either need to do Resident load or Preceding load. Which one are you employing in your application?

Preceding Load would look like this:

LotMonth:

LOAD *,

  Date(Date#('20' & Left(PSR_P2P3.TEXT.31, 2) & '-' &

  Pick(Match(Mid(PSR_P2P3.TEXT.31, 3, 1), 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M'), 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12), 'YYYY-MM'), 'YYYY-MM') as Month_of_Production ;

Load yourFields

From yourDataSource;

Resident Load will look like this:

LotMonth:

Load yourFields

From yourDataSource;

NewLotMonth:

LOAD *,

          Date(Date#('20' & Left(PSR_P2P3.TEXT.31, 2) & '-' &

          Pick(Match(Mid(PSR_P2P3.TEXT.31, 3, 1), 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M'), 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12), 'YYYY-MM'), 'YYYY-MM') as Month_of_Production

Resident LotMonth;

Drop Table LotMonth;

Which of the above two are you employing in your application?

[MarcoWedel]

Hi,

I had something similar in mind:

LOAD *,
    Date(Date#(Left([Lot Num],2)&'/'&(Ord(Mid([Lot Num],3,1))-64+(Mid([Lot Num],3,1)>'I')),'YY/M'),'YYYY-MM') as [Month of Production]
INLINE [
    Lot Num
    14G07
    12M18
    13A05
];

regards

Marco

[#6]

I needed to have a resident load and I didn't have one. Once I placed it in it is working for me. Thank you so much for your help.

[sunny_talwar]

Awesome

I am glad we were finally able to figure it all out

Best,

Sunny