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

[datanibbler]

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.

[marcus_sommer · Accepted solution]

Hi DataNibbler,

try something like this:

t1:

Load

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

From xyz

While iterno() < 5;

- Marcus

[datanibbler]

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

[datanibbler]

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!

[#5]

Hi,

Try using :

LOAD *,

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

Value

From abcd

While iterno() <= 5;

Regards,

Snehal Nabar

[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

[datanibbler]

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!

[datanibbler]

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

[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

[datanibbler]

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