Discussion board where members can get started with QlikView.
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,
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.
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:
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,
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
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 :
Location&'_'Day as Key,
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 :
Applymap('Map_hours', Key,'-') as Hours;
Location&'_'&weekday as Key;
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);
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.
Thanks! im going to try it now