topic Re: Start, end location. in QlikView

Start, end location.

markp201 — Wed, 12 Apr 2017 18:09:19 GMT

We have transaction with a start and end location. There are over 20,000 distinct locations

I have two dimension tables - StartLocations, EndLocations so I can't filter on a location and see start or end.

I have two create two filters. Ugh.

I read about canonical dates and thought this could be done for strings as well?

Also, can a mapping load can be used?

Re: Start, end location.

MarcoWedel — Thu, 13 Apr 2017 23:02:33 GMT

please post some sample data and your expected result.

Re: Start, end location.

markp201 — Fri, 14 Apr 2017 18:30:40 GMT

I have two locations in each fact but there is a single master location list. I would like to avoid two tables (option 1) but option 2 has the same # of rows as the fact table. Canonical may help but I haven't looked into this.

We have 100,000 in the source with 80,000 combined start/end locations. Across both locations- 20,000 (13,000 start, 7,000 end). Seems a bit wasteful to have the table repeated twice

Conceptual example (locations are actually around 40 characters)

Source

Key	StartLocation	EndLocation
1	A	B
2	A	C
3	B	C
4	B	A

Option 1 - my preference

Key	%StartLocation	%EndLocation
1	1	2
2	1	3
3	2	3
4	2	1

%StartLocation	StartLocation
1	A
2	B

%EndLocation	EndLocation
1	A
2	B
3	C

------------------------------------------------------------------------------------------------------------

Option 2

Key	%StartEndLocation
1	1
2	2
3	3
4	4

%StartEndLocation	StartLocation	Endlocation
1	A	B
2	A	C
3	B	C
4	B	A

Option 3 - Canonical location?

Re: Start, end location.

MarcoWedel — Sat, 15 Apr 2017 01:02:24 GMT

Hi,

maybe one solution could be:

table1:
CrossTable (LocationTypeTemp, Location)
LOAD Key,
    StartLocation,
    EndLocation
FROM [https://community.qlik.com/thread/256918] (html, codepage is 1252, embedded labels, table is @1);

Join
LOAD Distinct
    LocationTypeTemp,
    SubField(LocationTypeTemp,'Location',1) as LocationType
Resident table1;

DROP Field LocationTypeTemp;

hope this helps

regards

Marco

Re: Start, end location.

MarcoWedel — Wed, 19 Apr 2017 21:49:58 GMT

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco