Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey Everyone.
I have a problem which should be simple but is giving me headache's.
I will simplify the data I am struggling with.
I have 2 datasets,
Dataset 1 contains:
Revenue,
Dates,
Location.
and several other (currently not important) tables.
The thing this dataset misses however is Business hours.
We have 53 locations wich varry in business hours quite alot and even per day.
For example:
City / Location | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
Doetinchem JC | 15 | 15 | 15 | 15 | 15 | 16 | 13 |
Heerhugowaard JC | 19 | 19 | 19 | 19 | 19 | 19 | 19 |
Lemmer FC | 17 | 17 | 17 | 17 | 17 | 17 | 16 |
Meijel JC | 14 | 14 | 14 | 14 | 15 | 15 | 12 |
De Brug | 24 | 24 | 24 | 24 | 24 | 24 | 24 |
The numbers are the amount of open hours a day, as seen there is quite alot of variation in day and location.
So I made a tool in Excel that determines what day it is and makes a nice 3 kolom wide dataset with:
Location,
Dates
Openinghours
However when i try to load this into qlikview i will get a loop problem considering both Location and Dates exist in dataset 1.
If i rename lets say Dates, I get a nice connection as seen in the table Viewer with Location as key.
But if I make a date field it will not adjust anything, it will simply show the total hours of the entire dataset.
If i rename Location and keep Dates as Key it will respond on date fields but wont adjust anything to Location.
how the hell can i fix this, Syntax currently is:
XML_tmp:
NoConcatenate
Load
*
, Year([From date]) as [From Year]
, Month([From date]) as [From Month]
, Day([From date]) as [From Day]
, maketime(hour([Till date])) as [Till Time]
, Week([From date]) as Week
, Date([From date]) as Date
;
load
*
, ConvertToLocalTime([From date tmp],'GMT+02:00',1) as [From date]
, ConvertToLocalTime([Till date tmp],'GMT+02:00',1) as [Till date]
;
load
Kostenplaats
, Location
, MacName
, Result
,JackpotPaidCount
Resident MACRecord;
drop table MACRecord ;
Directory;
LOAD Location,
Date,
Hours,
FROM
[..\Beveiliging\Openingstijden Matrix.xlsm]
(ooxml, embedded labels, table is Backup);
Please help me!
Could you not just add Day of week field on both data sets.
I did try that, however i will get Sync issues as it will result in multiple Keys.
Use qualify on the problem field
hth
Hello Mike,
Since you want only one extra column of information added in your big table, I'd work with a mapping table for the hours information.
Then you first create the mapping in your script :
Map_hours:
MAPPING LOAD
Location&'_'Day as Key,
Hours
FROM.....
In order to do the mapping, you need to have a completely matching key prepared first before you can map. This can be done with a preceding load, and could look like :
Dataset 1
LOAD *,
Applymap('Map_hours', Key,'-') as Hours;
LOAD *,
Location&'_'&weekday as Key;
LOAD *
weekday(Dates) as weekday;
// (this will return 0 to 6 = monday to sunday so in your separate table, you may need to convert the text into numbers first);
LOAD
Revenue,
Dates,
Location,
others...
FROM .....
The result will be the extra information on hours added to your Dataset1 based upon location and day of the week, without any synthetic keys created.
Kind regards,
Johan
Thanks! im going to try it now