Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I think this is a tricky situation... at least for me 🙂
I need to sum the quantities of the "accessories" parts, by choosing the main reference.
Please see the example in the file attached.
Obrigado
Saudações de Cascais, Portugal!
I made a few changes to your load script.
1. In table "References Compatibility Products", I renamed "Main Reference" to "Reference Number".
2. In table "References Compatibility Products", I added a column called "Compatibility Seq". In your script, use the line
RowNo() as Compatibility Seq. This ensures that the resulting table is sorted the way yours is in the example.
3. I also added another resident load for table "References Compatibility Products" to add a record for each of the main Reference Numbers.
Adapt this script to your database loads and run it. Then create the table. If you filter on ABC, you'll get the correct result (see screenshot).
[Facts Table]: Load * Inline [ Doc Data, Doc Number, Reference, Qty 2/1/2018, 1801, ABC, 2 3/24/2018, 1802, ABD, 10 8/6/2018, 1803, ABC, 1 12/1/2018, 1804, ABG, 4 2/1/2018, 1801, CBC, 20 3/24/2018, 1802, CBD, 50 8/6/2018, 1803, CBC, 20 12/1/2018, 1804, CBG, 20 ]; [Reference Table]: Load * Inline [ Reference No, Reference Description ABC, PRINTER A ABD, PRINTER A TONER ABG, PRINTER A DRUM CBC, PRINTER C CBD, PRINTER C TONER CBG, PRINTER C DRUM ]; [References Compatible Products]: Load * Inline [ Reference No, Compatible Reference, Compatible Reference Description, Compatible Seq ABC, ABD, PRINTER A TONER, 1 ABC, ABG, PRINTER A DRUM, 2 CBC, CBD, PRINTER C TONER, 3 CBC, CBG, PRINTER C DRUM, 4 ]; [References Compatible Products]: Concatenate Load Distinct [Reference No], [Reference No] as [Compatible Reference], 99 as [Compatible Seq] Resident [References Compatible Products];
Data Section
Dim: [Reference No]
Dim: [Reference Description]
Dim [Compatible Reference] {Uncheck "Include Null Values"}
Measure: MaxString([Compatible Reference Description])
Measure: Sum(If(Reference=[Compatible Reference], Qty))
Sort Section
[Compatible Reference]: Change the sort on this to an expression. The expression is [Compatible Seq].
See attached example