Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Decoding lot data into date YYYY-MM

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


15 Replies

Re: Decoding lot data into date YYYY-MM

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


Employee
Employee

Re: Decoding lot data into date YYYY-MM

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

];

MVP
MVP

Re: Decoding lot data into date YYYY-MM

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

];

MVP
MVP

Re: Decoding lot data into date YYYY-MM

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

Re: Decoding lot data into date YYYY-MM

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] ;


Re: Decoding lot data into date YYYY-MM

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

Re: Decoding lot data into date YYYY-MM

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

Re: Decoding lot data into date YYYY-MM

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

Re: Decoding lot data into date YYYY-MM

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

Community Browser