topic Re: Limiting rows in App Development

Limiting rows

Gator — Tue, 08 Aug 2023 22:30:10 GMT

I have two tables, Table_A which has 1,000 rows and Table_B which has 10,000 rows. I want to restrict the number of rows I load in Table_B based on a value in a field in Table_A which corresponds to a value in a field in Table_B so that if the values are identical, I want to load that row into Table_B, otherwise not. The end result will be the same number of rows in both tables. The field names in the two table are completely different.

Re: Limiting rows

cristianj23a — Tue, 08 Aug 2023 23:05:34 GMT

Hello, here I am sending you a referential code with the case you are requesting:

1.- Create the Inline Data for Table_A and Table_B

Table_A:
LOAD * INLINE [
Field_A
Value1
Value2
Value3
// ... up to 1,000 rows
];

Table_B:
LOAD * INLINE [
Field_B
ValueX
ValueY
Value1
Value3
// ... up to 10,000 rows
];

2.- Create a Mapping Table Using the Values from Table_A

MappingTable:
MAPPING LOAD
Field_A
RESIDENT Table_A;

3.- Apply the Mapping to Table_B Using the Map Function

NewTable_B:
LOAD
Field_B,
ApplyMap('MappingTable', Field_B, 'No Match') as MatchedField
RESIDENT Table_B;

4.- Filter Table_B Based on the Mapping Result

FinalTable_B:
LOAD *
RESIDENT NewTable_B
WHERE MatchedField <> 'No Match';

DROP TABLE NewTable_B;

The ApplyMap function is used to map the values of Field_B in Table_B to the values in Field_A in Table_A using the mapping table. Rows with no matching value will have 'No Match', and you can filter these out in the final table.

The resulting FinalTable_B will have the same number of rows as Table_A, based on the matching values.

Make sure to adjust the field names and values according to your actual data and scenario.

Regarts.

Cristian R.

Re: Limiting rows

BrunPierre — Wed, 09 Aug 2023 00:44:10 GMT

Hi, as below using Exists()

Table_A:
LOAD Value_A,

FROM ...;

Table_B:
LOAD Value_B

FROM ...
Where Exists(Value_A,Value_B);

DROP Table Table_A;

Re: Limiting rows

Tanish — Wed, 09 Aug 2023 16:13:22 GMT

Hi,

There is two ways to resolve this problem.

1. Apply Map - You can create a Mapping Load of First Table

then use this in Second table then in the preceding load u can use where condition to keep only the records which you got from applymap

2. You can also use Where exist function.

Hope this will help....

Re: Limiting rows

Gator — Wed, 09 Aug 2023 21:04:32 GMT

Hi Christian - Thanks for your reply. I think in step 2 you're missing a second value for the mapping table. Thanks.