Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
markp201
Creator III
Creator III

Start, end location.

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?

4 Replies
MarcoWedel

please post some sample data and your expected result.

markp201
Creator III
Creator III
Author

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

KeyStartLocationEndLocation
1AB
2AC
3BC
4BA

Option 1 - my preference

Key%StartLocation%EndLocation
112
213
323
421

%StartLocation
StartLocation
1A
2B


%EndLocationEndLocation
1A
2B
3C

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

Option 2

Key
%StartEndLocation
11
22
33
44


%StartEndLocationStartLocationEndlocation
1AB
2AC
3BC
4BA


Option 3 - Canonical location?

MarcoWedel

Hi,

maybe one solution could be:

QlikCommunity_Thread_256918_Pic1.JPG

QlikCommunity_Thread_256918_Pic2.JPG

QlikCommunity_Thread_256918_Pic3.JPG

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