6 Replies Latest reply: Apr 10, 2015 9:01 AM by Joseph Simmons RSS

    Formatting problem?

    Friedrich Hofmann

      Hi,

       

      I am facing an issue I just cannot get to the bottom of:

      - I have a file where there is a date_value (originally in the format of 'DD.MM.YYYY') in every row;

      - I can easily format that to the format of 'YYYYMMDD' (to avoid the dots which could be problematic in other functions)

      - (Background: That file is an aggregation of my base_file)

      - Then I want to open a loop and parse that small file line by line (day by day, that is) and extract all info from the base_file where I have
         that date.

      - In the WHERE clause, I use the exact same date_format (I have to reformat it, but I know that that works as the file_to_parse also comes from that same base_file)

      <=> Still I get 0 records!

       

      I will attach two things to this post:

      - The qvd file I have created from that base_file

      - The script code I am currently using

       

      Thanks a lot for any help here!

      Best regards,

       

      DataNibbler

       

      P.S.: I have checked several times that my date_variable is numeric, just like the field in the table, so I cannot understand why comparing them should produce 0 records ...

        • Re: Formatting problem?
          Rudolf Linder

          I made a Little test with yur script and with a small inline table

          Ergebnisse_Vgl_#1:
          Load * Inline [
          Versanddatum_BMW, Endkosten
          01.04.2015, 1000
          02.04.2015, 2000
          ]
          ;

           

          The result is also 0 datarows.

          but when i Change Tagesdatum2 to Tagesdatum (which still is in the old data Format), I get 1 datarow

          I donot know why comparison of 2 different dateformats is working (YYYYMMDD compared to DD.MM.YYYY')

           

            LET v_current_date = PEEK('Versanddatum', 0, 'Ergebnis_#1_Tagessummen');

          Does it help in your case too?

          • Re: Formatting problem?
            Joseph Simmons

            Hi Data,

             

            looks like Versanddatum2 is in the YYYYMMDD format? but in your QVD Versanddatum_BMW has the decimals.

            So don't think you will just be able to date your where clause.

             

            You'll need to use Date# first then date in your where clause

             

            DATE( DATE#(Versanddatum_BMW, 'DD.MM.YYYY'), 'YYYYMMDD')

             

            hope that helps

            Joe

              • Re: Formatting problem?
                Friedrich Hofmann

                Hi,

                 

                it works now - but it's very strange and the worst is, I have no idea why it works now and didn't work before ...

                I wrapped the date (in the old format 'DD.MM.YYYY') in quoted and in another DATE() function now - that way works.

                => It now looks like >> DATE('$(v_current_date)')  <<

                 

                @ JoeSimmons

                 

                That with the decimals could be - but actually I use the very same DATE() function on the very same table twice - first I aggregate the base_file to a table holding all the distinct dates and then I parse that to split the very same base_file into slices - so there's no apparent reason why the results should not be comparable ... I'll try anyway.

                  • Re: Formatting problem?
                    Joseph Simmons

                    Hmm interesting, I was thinking the decimals was causing problems if it wasn't treated the value as a proper number and so failing the date function. If that's not the case though I'm not sure, would be good if you could upload an example or your full script.

                    As you say should be fine if it's form the same base file

                     

                    Joe

                      • Re: Formatting problem?
                        Friedrich Hofmann

                        Hi Joe,

                         

                        that's very simple: If you load all the fields from the qvd file I have attached - that is the base_file (I have a STORE command just before that piece of code I attached).

                        Best regards,

                         

                        DataNibbler

                          • Re: Formatting problem?
                            Joseph Simmons

                            Try the below, as it's a real date field there will be a numerical value underlying, so should be able to use num instead

                             

                             

                            LET v_current_date = Num(PEEK('Versanddatum2', 0, 'Ergebnis_#1_Tagessummen'));

                            Drop table Ergebnis_#1_Tagessummen;



                            //  LET v_current_date = DATE('$(v_current_date)', 'YYYYMMDD');

                            BMW_only_Tagessumme:

                            NoConcatenate

                             
                            LOAD

                                  *,

                                 
                            DATE(Versanddatum_BMW, 'YYYYMMDD') as Versanddatum_BMW_3

                            RESIDENT Ergebnisse_Vgl_#1

                            WHERE Num(Versanddatum_BMW) = '$(v_current_date)'

                              ;