Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Combining Data

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 / LocationMondayTuesdayWednesdayThursdayFridaySaturdaySunday
Doetinchem JC15151515151613
Heerhugowaard JC19191919191919
Lemmer FC17171717171716
Meijel JC14141414151512
De Brug24242424242424

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!

5 Replies
sgrice
Partner - Creator II
Partner - Creator II

Could you not just add Day of week field on both data sets.

Not applicable
Author

I did try that, however i will get Sync issues as it will result in multiple Keys.

sasiparupudi1
Master III
Master III

Use qualify on the problem field

hth

puttemans
Specialist
Specialist

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

Not applicable
Author

Thanks! im going to try it now