Divide number for nr. of rows in another table with that ID

[Federicofkt]

Hi,

I have two tables, one is quite simple, 3 columns and 3 rows: ID, Cost1, Cost2

In the second one I have more data, included the ID. What I need to do is to assign for each row of table 2 with that ID, the cost1 and cost2 from the first table divided for the amount of rows in table 2 with that ID.

I've tried something like this:

NoConcatenate
CountRows:
LOAD
Id,
Count(Id) AS NumRows
RESIDENT Tab2
GROUP BY Id;

NoConcatenate
Table2:
LOAD *
,ApplyMap('MAP_ZONACOSTOSTIP',Id, 0) / NumRows as Cost1
,ApplyMap('MAP_ZONACOSTOFULL',Id, 0) / NumRows as Cost2
RESIDENT Table2Tmp;
LEFT JOIN (CountRows)
LOAD
*
RESIDENT CountRows;

But it throws the error that it's not able to find NumRows.

Any help appreciated.

Thanks!

[diegozecchini]

Hi!
To achieve your goal, you'll need to ensure that NumRows (the count of rows per ID in Table2) is correctly calculated and available during the computation of Cost1 and Cost2. The issue with your code is likely related to how you're joining NumRows and then referencing it.

Here’s how you can solve this
First of all let's calculate the Count of Rows per ID (NumRows) creating a table to calculate how many rows exist for each ID in Table2.
then add this NumRows column to Table2 and use the NumRows to divide the respective costs from Table1.

Here’s the code I propose

// Step 1: Calculate the count of rows per ID
CountRows:
LOAD
Id,
Count(Id) AS NumRows
RESIDENT Table2Tmp
GROUP BY Id;

// Step 2: Add the count (NumRows) to Table2
NoConcatenate
Table2:
LOAD
*,
ApplyMap('MAP_ZONACOSTOSTIP', Id, 0) / NumRows AS Cost1,
ApplyMap('MAP_ZONACOSTOFULL', Id, 0) / NumRows AS Cost2
RESIDENT Table2Tmp;
LEFT JOIN (Table2)
LOAD
Id,
NumRows
RESIDENT CountRows;

// Step 3: Drop temporary tables
DROP TABLE CountRows;
DROP TABLE Table2Tmp;

Ensure MAP_ZONACOSTOSTIP and MAP_ZONACOSTOFULL are correctly defined as mappings from Table1.
If the issue persists, verify the data in Table2Tmp and Table1 for inconsistencies.
Let me know if you need further clarification or adjustments!

[sbaro_bd]

Hi @Federicofkt ,

It's because your field NumRows  doesn't exist in the table Table2Tmp. According to your script, this field is only available in the table CountRows.

I don't know how your model is structured, but you can't try something like this :

CountRows:
LEFT JOIN (Table2Tmp)
LOAD
Id,
Count(Id) AS NumRows
RESIDENT Tab2
GROUP BY Id;

NoConcatenate
Table2:
LOAD *
,ApplyMap('MAP_ZONACOSTOSTIP',Id, 0) / NumRows as Cost1
,ApplyMap('MAP_ZONACOSTOFULL',Id, 0) / NumRows as Cost2
RESIDENT Table2Tmp;

Regards. 

[Federicofkt]

I think that's the code ChatGPT proposed, not you.

If I'm wrong please explain how can I use NumRows that is stored in CountRows table by using Resident Table2Tmp. It will throw the field not found error.

I'm searching for valid anwers, I can use ChatGPT by myself

[jchx]

Why don't you create a measure for this on the front end while maintaining your current data model ? More efficient that way: 

ID_Cost:
Load * inline [
ID, Cost1, Cost2
A, 5, 7
B, 2, 8
C, 3, 9
];

ID_Details:
Load * inline [
ID, Detail
A, 1
A, 3
A, 5
B, 2
B, 6
C, 4
C, 1
C, 2
C, 3
];