Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
Highlighted
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
Highlighted
Partner
Partner

Re: Combining Data

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

Highlighted
Not applicable

Re: Combining Data

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

Highlighted
Honored Contributor III

Re: Combining Data

Use qualify on the problem field

hth

Highlighted
Valued Contributor

Re: Combining Data

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

Highlighted
Not applicable

Re: Combining Data

Thanks! im going to try it now