15 Replies Latest reply: Jun 2, 2015 7:52 AM by Sunny Talwar RSS

    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

        • Re: Decoding lot data into date YYYY-MM
          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


            • Re: Decoding lot data into date YYYY-MM
              Stefan Wühl

              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

                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

                    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

                        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

                            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

                                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

                                    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

                                        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

                                            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
                              Clever Anjos

                              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
                                Massimo Grossi

                                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

                                ];