Combining fields from separate tables

[Kenvis]

In my project I have additional fields on the 3 tables but keep things simple an example of the data is below.

I need to combine the stock_number and serial_number and count the number of duplicates. In this example the only duplicate would be stock number AAA with serial number 111.

I have the 3 tables inner joined but cannot figure out how to get a correct count on my sheet. I am not sure if I need to load my data a different way or if there is a function I am not finding to combine the fields and count the duplicates.

Any help would be greatly appreciated!

[sidhiq91]

@Kenvis  Please find the below code that is used in the back end first.

NoConcatenate
Table1:
Load * inline [
Key1, Stock_number
1, AAA
2, BBB
];

Inner join (Table1)
Load * inline [
Key1, Key2
1,9
2,8
1,7
];

Inner join (Table1)
Load * inline [
Key2, Serial_number
9,111
8,222
7,333
9,111
];

NoConcatenate
Table2:
Load
Stock_number & Key2 & Serial_number as stock_serial_value,
Serial_number,
Stock_number,
Key1,
Key2
Resident Table1;

Drop table Table1;

Exit Script;

In the front end. Create an expression as shown below:

And when click on one of the Stock Number or serial number. It will show the duplicates.

If this resolves the issues, please like and accept it as a solution.

[PedroNetto]

Count the concatenation of the fields Serial_number and Stock_number

Expression: Count( Serial_number & Stock_number)

Result:

[Kenvis]

@PedroNetto @sidhiq91 Both of these were very helpful.

My issue now is only displaying the duplicates instead of displaying all data. 

[PedroNetto]

Table1:
Load * inline [
Key1, Stock_number
1, AAA
2, BBB
];

Inner join (Table1)
Load * inline [
Key1, Key2
1,9
2,8
1,7
];

Inner join (Table1)
Load * inline [
Key2, Serial_number
9,111
8,222
7,333
9,111
];

NoConcatenate

Table2:
Load
count( Stock_number & Serial_number) as stock_serial_value,
Serial_number,
Stock_number
Resident Table1 Group by Serial_number, Stock_number;

Drop table Table1;

NoConcatenate

Table3:
LOAD
Serial_number,
Stock_number
Resident Table2 Where stock_serial_value > 1;
Drop table Table2;

Exit Script;

Result:

[Kenvis]

@PedroNetto this would work but I need to load all the data for the other graphs/charts. I have a graph where I only want to show duplicated stock and serial numbers. 

[PedroNetto · Accepted solution]

@Kenvis now all fields are avaiable and i created a new field to get duplicates

Table1:
Load * inline [
Key1, Stock_number
1, AAA
2, BBB
];

Inner join (Table1)
Load * inline [
Key1, Key2
1,9
2,8
1,7
];

Inner join (Table1)
Load * inline [
Key2, Serial_number
9,111
8,222
7,333
9,111
];

NoConcatenate

Table2:
LOAD
*,
if(stock_serial_value > 1, 'Y', 'N') as Duplcate;
Load
count( Stock_number & Serial_number) as stock_serial_value,
Serial_number,
Stock_number,
Key1, Key2
Resident Table1 Group by Serial_number, Stock_number, Key1, Key2;

Drop table Table1;
EXIT Script;

Result: