topic Re: Working with Lookup and Summary tables in App Development

Working with Lookup and Summary tables

Lena4 — Thu, 06 Feb 2025 17:32:38 GMT

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.

Re: Working with Lookup and Summary tables

marcus_sommer — Fri, 07 Feb 2025 07:59:33 GMT

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.

Re: Working with Lookup and Summary tables

JandreKillianRIC — Fri, 07 Feb 2025 09:17:39 GMT

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

Re: Working with Lookup and Summary tables

Lena4 — Thu, 13 Feb 2025 23:40:14 GMT

@JandreKillianRIC Thanks for breaking this down!

Re: Working with Lookup and Summary tables

JandreKillianRIC — Fri, 14 Feb 2025 05:47:34 GMT

Hi @Lena4

Did this work for you?

Regards Jandre