Working with Lookup and Summary tables

[Lena4]

Hi All - I'm hoping you can help me out figure out how to accomplish this. I've tried several things, but I'm missing something. I have two tables. One has timestamps, IDs, Values and locations.   I want to look at each time/location and determine if the ID combinations in table2 are present.  The only thing in common between the two is the location.

In the example below, IDs  1&2 are 'active', so I look at table 2 and determine that corresponds with sublocation 'Area1A"

In the future, I'll need to add a lot more Locations & Sub-locations, so I'm hoping for a solution that keeps that in mind.

[marcus_sommer]

Just making the lookup against the location isn't enough else you need to include more information like the ID. You may reach this by loading Table 2 with a crosstable-statement to push all ID information into a single field and then you will be able to combine the location and the ID within the lookup-value.

[JandreKillianRIC]

Hi @Lena4 

I built this out can could see that we need something like a line reference as the result table doesn't work doing a lookup. I also noticed that all the ID's need to match (But on a line Level) Here is my result table (And your 2) 

Here is my Script 

Table1Tmp:
Load 
	Timestamp(Timestamp) as Timestamp,
    ID, 
    Value, 
    Location; 
Load * Inline [
Timestamp,ID,Value,Location
45658.5,1,Active,A
45658.5416666667,2,Active,A
45658.5416666667,3,Active,A
45658.5416666667,6,Active,B
45658.625,1,Active,A
45658.625,2,Active,A
45658.75,5,Active,A
45658.75,3,Active,A
45658.8333333333,1,Active,B
45658.8333333333,2,Active,B
45658.8333333333,4,Active,B
];

Table1:
Load 
	Timestamp,
    Location,
    Concat(DISTINCT ID, '|') as IDs
Resident Table1Tmp
Where Len(Trim(ID))
Group By 
	Timestamp,
    Location;

Drop Table Table1Tmp; 

Table2Tmp:
Load 
	RowNo() as RowID,
	*;
Load * Inline [
Location,Sub-Location,ID1,ID2,ID3
A,Area1A,5,3,2
A,Area1A,2,3,
A,Area1A,5,3,
A,Area2A,2,4,
A,Area2A,1,2,
B,Area1B,1,2,4
B,Area1B,6,,
B,Area2B,,,
];



Table2Tmp2:
CrossTable(IDVal,ID, 3)
Load 
	*
Resident Table2Tmp;

Drop Field IDVal from Table2Tmp2;

Drop Table Table2Tmp; 

Table2:
Load 
	[Sub-Location],
    Location,
    RowID,
    Concat(DISTINCT ID, '|') as IDs
Resident Table2Tmp2
Where Len(Trim(ID))
Group By 
	[Sub-Location],
    Location,
    RowID;

Drop Table Table2Tmp2; 

Please note this has a Syn Key (But you can remove that and just make a composite key using the Location and IDs field. 

For the table > Dimension = Timestamp and the Measures 

Sub Location A = MaxString(IF(Right([Sub-Location],1) = 'A', [Sub-Location]))
Sub Location B = MaxString(IF(Right([Sub-Location],1) = 'B', [Sub-Location]))

Regards Jandre

[Lena4]

@JandreKillianRIC Thanks for breaking this down!

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download

[JandreKillianRIC]

Hi @Lena4 

Did this work for you? 

Regards Jandre