Start, end location.

[markp201]

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?

[MarcoWedel]

please post some sample data and your expected result.

[markp201]

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?

[MarcoWedel]

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

[MarcoWedel]

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco