Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Decoding lot data into date YYYY-MM

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

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Table:

LOAD *,

  Date(Date#('20' & Left([Lot Num], 2) & '-' &

  Pick(Match(Mid([Lot Num], 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 * Inline [

Lot Num

14G07

12M18

13A05

];


Output:

Capture.PNG


View solution in original post

15 Replies
sunny_talwar

Try this:

Table:

LOAD *,

  Date(Date#('20' & Left([Lot Num], 2) & '-' &

  Pick(Match(Mid([Lot Num], 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 * Inline [

Lot Num

14G07

12M18

13A05

];


Output:

Capture.PNG


Clever_Anjos
Employee
Employee

Please check

LOAD [Lot Num],

  MakeDate(

  2000+left([Lot Num],2),

  if(mid([Lot Num],3,1) precedes 'I',

  ord(mid([Lot Num],3,1)) - ord('A') + 1,

  ord(mid([Lot Num],3,1)) - ord('A'))) as Month INLINE [

    Lot Num   ,      Month of Production

    14G07      ,      2014-07

    12M18       ,     2012-12

    13A05      ,       2013-01

];

maxgro
MVP
MVP

s:

load

  *,

  date(

  makedate(

  2000 + left([Lot Num],2),

  pick(match(mid([Lot Num],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') as NewField

inline [

Lot Num  ,       Month of Production

14G07     ,       2014-07

12M18      ,      2012-12

13A05       ,      2013-01

];

swuehl
MVP
MVP

You can even leave out the pick() from sunindia's solution, because match() will return an index that fits our needs.

Table:

LOAD *,

  Date(Date#('20' & Left([Lot Num], 2) & '-' &

  Match(Mid([Lot Num], 3, 1), 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M'), 'YYYY-MM'), 'YYYY-MM')

          as [Month of Production]  ;

LOAD * Inline [

Lot Num

14G07

12M18

13A05

];

Not applicable
Author

I tried adapting this to my situation, however, this is a large report and the lot numbers are always in production and I couldn't seem to get it to work.  I took out the inline load of the three I gave as an example and used a reference to my field that [Lot Num] but it broke.

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

What do you mean when you say broke? Does it give you an error or does it give you an output you are not expecting??? Not sure what isn't working.

Best,

Sunny

Not applicable
Author

Sorry about that.  I should have been more specific.  When I used the modified code that I posted above, It gave no error when reloading but it didn't create a new field of "[Month of Production]"  Ideally, I want to add an additional dimension to an already exiting Table box where I have the data coming in for the lot number "PSR_P2P3.Text31"  and convert it into a production Year and month.  The code you provided works great when just loading the three values but I couldn't figure out how to adapt it to my data pulling in from the database.

Thanks,

Sean

sunny_talwar

You don't have to be sorry bud . I was just not sure what went wrong.

So it isn't even showing Month of Production, huh!!!. Are you using this table as a resident load later in your script and dropping the original table may be? because I don't see why else it won't show you the new created table in your application. Can you verify the above thing?

Best,

Sunny

Not applicable
Author

The field was showing with your original script but when I modified your script to better fit my application that is when i broke it and the field is no longer available.  I have no resident load of the data and i am not dropping any tables in my script.  I actually just started on this script to see if i could convert the lot number to a date so the script is very clean. I loaded a QVW of our data model from our server to my local client and the only code in the script is my binary load and the code that you provided with my modification.

Thanks,

Sean