topic Re: Combining Data in QlikView

Combining Data

Thu, 23 Jul 2015 11:55:54 GMT

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!

Re: Combining Data

sgrice — Thu, 23 Jul 2015 12:32:53 GMT

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

Re: Combining Data

Thu, 23 Jul 2015 12:34:26 GMT

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

Re: Combining Data

sasiparupudi1 — Thu, 23 Jul 2015 12:37:53 GMT

Use qualify on the problem field

hth

Re: Combining Data

puttemans — Thu, 23 Jul 2015 12:57:41 GMT

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

Re: Combining Data

Thu, 23 Jul 2015 13:08:09 GMT

Thanks! im going to try it now