Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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:
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
];
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
];
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
];
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] ;
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
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
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
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