9 Replies Latest reply: Feb 3, 2015 3:40 AM by Friedrich Hofmann RSS

    Date function - I have a cw and I need a date

    Friedrich Hofmann


      Hi,

       

      I have the requirement of adding to a table where I have, for every day, a number of "man-hours" in the early shift and one in the late shift.

       

      To that, I need to add from a second table (derived from the personnel-plans of which there are 2 for each week (one for early shift, one for late shift) - in that, I have a year (derived from the file_name) and a calendar_week (and a shift).

       

      => Now I have to "pump up" that second table so that I can add its contents to those of the first - I need to generate all five working_days of a calendar_week out of the same (cw).

       

      Can anybody give me a hint how to do this?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

       

      P.S.: I think I know a way, even if it's maybe not the most elegant/ slim one: I have a master_calendar - the code is in a qvs_file as it's used everywhere - where I have both the cw and the date. So I can use that.

        • Re: Date function - I have a cw and I need a date
          Marcus Sommer

          Hi DataNibbler,

           

          try something like this:

           

          t1:

          Load

               makeweekdate(Year, Week, iterno() - 1) as Date, Value

          From xyz

          While iterno() < 5;

           

          - Marcus

            • Re: Date function - I have a cw and I need a date
              Friedrich Hofmann

              Hi Marcus,

               

              many thanks!

              I didn't know this function. I have been through all the Date-and-Time-functions several times before (which are, in my outdated version of QlikView, all on one page in the help_file), but I have never really noticed that one.

               

              Best regards,

               

              DataNibbler

                • Re: Date function - I have a cw and I need a date
                  Friedrich Hofmann

                  Hi Marcus,

                   

                  I seem to have some issue here:

                  - I have used that code quite exactly like you posted it:

                   

                  Tage generieren:

                  LOAD

                        Makeweekdate(Jahr_StSt_WE, KW_StSt_WE, iterno()-1) as Datum_StSt_WE

                  RESIDENT Staplerfahrer_WE

                  WHILE iterno() < 5;

                  (that should be <=5, I just notice)

                   

                  The problem is, I load RESIDENT from the table I have generated out of the personnel_plans - but in that, I have about 20 lines per cw - 2 shifts, several employees of course - so by doing this RESIDENT, I get 4 workdays for every line in that table, not only for every cw.

                  Is there any way round this without creating a temporary aggregated table?

                   

                  P.S.: OK, I'll just squeeze in a >> LOAD DISTINCT << with just those two fields, run that little code and drop that temp_table again. Then my figures should line up. Every cw should then be joined with 5 records, so I should end up with (700*5) = 3.500 records.

                   

                  P.P.S.: ... and I do. Hurray!

                    • Re: Date function - I have a cw and I need a date
                      Marcus Sommer

                      Hi DataNibbler,

                       

                      it's difficult to say which way might be the best. Maybe a loop outside which runs through the shifts or you reduced your table before the while-load and then extend this again per joining or mapping - but I don't think it would be easier or save time as to use a temporary aggregated table - sometimes it's good to be pragmatic.

                       

                      - Marcus

                        • Re: Date function - I have a cw and I need a date
                          Friedrich Hofmann

                          Hi,

                           

                          well, it works, so I'm happy for the moment.

                          Now I have quite another issue:

                          That same code, which works fine, in one of my apps, suddenly seems to not work when I copy it into one of our main data_loading apps. That's very strange. The file_paths can't be it anymore, I have already made them all explicit - no relative paths anymore.

                          Anyway, this one is solved.

                           

                          Thanks a lot!

                            • Re: Date function - I have a cw and I need a date
                              Friedrich Hofmann

                              Hi Marcus,

                               

                              somehow I can't quite get behind this:

                              There is a date_filter in a LOAD (in a FOR EACH loop) that should actually load 2 Excel_files.

                              In one of my apps, that works fine. In the other, that LOAD returns 0 lines - when I remove the date_filter, it returns 2, so that is the problem all right.

                               

                              The filter looks like this:

                              >> WHERE Date(Floor(Filletime()), 'DD.MM.YYYY') > '30.05.2014' <<

                               

                              The difference is obvious: In the app where it works, the date_format (on the "Main" tab) is set to 'DD.MM.YYYY'; In the other (where it doesn't work), it is set to 'DD/MM/YYYY'

                              <=> but isn't that only a display_format for the GUI? Isn't a date (or timestamp as it is here) always stored in numeric format?

                              Alternatively, I could just use the numeric value.

                              Best regards,

                               

                              DataNibbler

                                • Re: Date function - I have a cw and I need a date
                                  Marcus Sommer

                                  I think the reason is the default date-format in this app which don't led to a numeric interpretation from '30.05.2014' and therefore the where-clause will fail, try it instead with:

                                   

                                  WHERE Date(Floor(Filletime()), 'DD/MM/YYYY') > '30/05/2014'

                                   

                                  or use directly numeric conditions:

                                   

                                  WHERE num(Floor(Filletime())) >41789

                                   

                                  whereby the format-functions by default-formats or numeric functions like max() or floor() shouldn't be necessary.

                                   

                                  - Marcus

                                    • Re: Date function - I have a cw and I need a date
                                      Friedrich Hofmann

                                      Hi Marcus,

                                       

                                      I don't quite understand the background of this which is kind of annoying - but I do understand why it works one way and not another.

                                      What you suggest was just my thought actually - I was thinking about adapting the date_format, but I settled on using numeric values instead.

                                      So I constructed that date using num(MAKEDATE()) and I now have the filter just like your second proposal - without the num() around it.

                                       

                                      Thanks a lot!

                                       

                                      Best regards,

                                       

                                      DataNibbler

                        • Re: Date function - I have a cw and I need a date
                          Snehal Nabar

                          Hi,

                           

                          Try using :

                          LOAD *,

                          makeweekdate(Year, Week, iterno() - 1) as NewDate,

                          Value

                          From abcd

                          While iterno() <= 5;

                           

                          Regards,

                          Snehal Nabar