Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi DataNibbler,
try something like this:
t1:
Load
makeweekdate(Year, Week, iterno() - 1) as Date, Value
From xyz
While iterno() < 5;
- Marcus
Hi DataNibbler,
try something like this:
t1:
Load
makeweekdate(Year, Week, iterno() - 1) as Date, Value
From xyz
While iterno() < 5;
- Marcus
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
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!
Hi,
Try using :
LOAD *,
makeweekdate(Year, Week, iterno() - 1) as NewDate,
Value
From abcd
While iterno() <= 5;
Regards,
Snehal Nabar
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
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!
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
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
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