topic Re: Decoding lot data into date YYYY-MM in QlikView

Decoding lot data into date YYYY-MM

Mon, 01 Jun 2015 18:39:58 GMT

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

Re: Decoding lot data into date YYYY-MM

sunny_talwar — Mon, 01 Jun 2015 18:56:15 GMT

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:

Re: Decoding lot data into date YYYY-MM

Clever_Anjos — Mon, 01 Jun 2015 19:02:10 GMT

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

];

Re: Decoding lot data into date YYYY-MM

maxgro — Mon, 01 Jun 2015 19:06:56 GMT

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

];

Re: Decoding lot data into date YYYY-MM

swuehl — Mon, 01 Jun 2015 19:07:23 GMT

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

];

Re: Decoding lot data into date YYYY-MM

Mon, 01 Jun 2015 19:12:49 GMT

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

sunny_talwar — Mon, 01 Jun 2015 19:21:46 GMT

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

Re: Decoding lot data into date YYYY-MM

Mon, 01 Jun 2015 19:31:33 GMT

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

sunny_talwar — Mon, 01 Jun 2015 19:36:46 GMT

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

Re: Decoding lot data into date YYYY-MM

Mon, 01 Jun 2015 19:43:03 GMT

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

Re: Decoding lot data into date YYYY-MM

sunny_talwar — Mon, 01 Jun 2015 19:46:09 GMT

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

Re: Decoding lot data into date YYYY-MM

Mon, 01 Jun 2015 20:24:26 GMT

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 ;

Re: Decoding lot data into date YYYY-MM

sunny_talwar — Mon, 01 Jun 2015 20:29:41 GMT

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?

Re: Decoding lot data into date YYYY-MM

MarcoWedel — Mon, 01 Jun 2015 22:15:13 GMT

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

Re: Decoding lot data into date YYYY-MM

Tue, 02 Jun 2015 11:51:19 GMT

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.

Re: Decoding lot data into date YYYY-MM

sunny_talwar — Tue, 02 Jun 2015 11:52:08 GMT

Awesome

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

Best,

Sunny