Hi All,
I have created Calculation View and considered 2 tables inside projections and joined them with Left outer join.
Data count of Left side Table: 51731
Data count of right side table: 3760
Type of join: Left outer Join
There is a join (Left Outer join) condition between two tables based on Document Number.
But out put of join is not matching with data count of Left side table which is 51731. It is giving more records (64874) than actual (51731).
What would be the reason?
What I should do to get correct count .
Are primary key combination should match between two tables?
Thanks in Advance.
- SAP Managed Tags:
Accepted Solutions (1)
Hi Ram,
The problem happened due to duplicate Entries in Projection_3 Node.
WORKCENTER and PRODORDER column have multiple values for same DOC_NUMBER, MATERIAL and MAT_PLANT fields.
For example your data is like in Projection_3 Node
If your Projection_1 Node have only 1 row with (1, MAT_A and BLR entries), By Performing this LO Join Operation It will have Join Output as 3 rows.
I hope you could get the cause of the problem.
To avoid this Problem,
Make sure you have unique Entries (By 3 columns combination) in Projection_3 node.
You can use Filter operation or Latest_Time stamp operation to get the unique count of records.
Best Regards,
Muthu
Answers (4)
Hi there,
There is a very simple solution to the above mentioned problem.
It is so simple that experienced SAP Hana consultants sometimes forget to tell you.
The solution is: use an Aggregation in between, before the join, and make all measure fields (key figure fields) Aggregated (with right mouse click on the field in the aggregation output pane).
You can see a small Greek sum sign (Σ) added to the field icon.
The good thing now is that the calculation engine aggregates before joining.
Without this setting the end result is aggregated, however, the join still uses the pre-aggregated records, which might lead to multiplication of the measure values (amounts or quantities) after the join.
For the newbees: Leave out all fields that are different in the unaggregated records. The aggregation is made only if all dimension values are the same.
To this end I 'removed' Line Item Number (DOCLN) by making it an aggregated column with aggregation type Count (also with right mouse click). Logically, sum aggregation is not available for dimension fields. Count, max, min are available, as you can see in the properties pane at the bottom.
I used the Rank node to cover the situations where even after aggregation there still can be multiple lines.
Just be selecting the first (or last) line. First or last is determined by the ascending or descending sort setting in the rank settings.
Be aware to determine the correct Partition. This is the key within which you want the system to determine the rank.
In my case that is FI document number. The key for FI document number is a compounded key (not just document number, BELNR), consisting of multiple dimensions. Obviously, I leave out the document item field (DOCLN) in the partition, as I want to rank within the FI document over the line items of the document.
The rank 'trick' is only useful if you do not need the measure values from the records that are skipped in your join results (e.g. when all measures are coming from the other branch, as in my case).
I hope this can be of some help for the newbees in the SAP HANA community, as the community is a great help to me.
Have fun!
| User | Count |
|---|---|
| 87 | |
| 10 | |
| 10 | |
| 10 | |
| 7 | |
| 6 | |
| 6 | |
| 5 | |
| 5 | |
| 4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.