Suggestion for Row count option in HCI DS

[former_member423067]

Hello All,

I'm generating an output file from IBP and placing it in SFTP folder. The Trailer line in the file has the total record count.

I use gen_row_num() Function to calculate the Final Row count in the last Transform while generating the output file.

The Problem is, the Row count is getting populated wrong randomly.

on further analysis, I noticed, when the rows are sorted wrongly. The Row count is picked as the last row sorted.

for example, if there total 100 records. count is passed as 9,10 etc. 99 % this is populated correctly.

Is this a bug ? any suggestions to get the count correctly, without using another Aggregate Transform in the flow.

with regards, Nazeer

[former_member423067]

Hello Jeff,

Any suggestion to populate Total Record count in the file output.

use of gen_row_num() in the Trailer column in the Target Transform not working always.

I tried changing Degree of Parallelism to 1. still now change. The output is very random and not reliable.

with regards,

Nazeer

[former_member423067]

In the current design, there no Aggregate Transform.

Below is sample output.

Material|Plant|Ship to party|Ship by date|IBP proposed quantity Coverage|UOM

10000001|LOC1|SHIPTOLOC1|01/31/2020|5600|EA

10000001|LOC1|SHIPTOLOC2|01/24/2020|56000|EA

EndofFile|RecordCount|2

Trailer column mapping is - 'EndofFile|RecordCount|' || gen_row_num()

Record count is not happening working randomly.

[jeff_k]

Thanks, I think the function might not work in this application because it only increments each time it is called. The footer is written at the end of the dataflow, so only one or apparently two times (maybe header counts as well). You could try the count() function instead, which should check a given column for non-null values and give the returned number. I think this could work on an entire dataset, but I haven't tested yet. I will update here once I can implement it, but could be some time.

[former_member423067]

Thank You Jeff,

I'm trying to avoid use of additional Aggregate Transform. Is there any way, we can sort the records in ASC order and ensure, the last records read is always generated the Record Count.

Count() is Function under Aggregate Transform only.

If no choice, I would go ahead with Count() and Agg Transform.

[former_member423067]

Hello Jeff,

I already tried below options:

1. 'EndofFile|RecordCount|' || gen_row_num() in the last Transform - Target Query .

2. gen_row_num() in last before transform and map to field "Count" . then apply sort by Col "Count" field in ASC order in last Transform.

3. use gen_row_num_by_group(Transform3.UOMID) (UOM is EA always) in last before transform and map to field "Count" . then apply sort by Col "Count" field in ASC order in last Transform.

all the options work most of the time but randomly failing.

with regards,

Nazeer

[jeff_k]

Is there an aggregation or not? And can you give a few example lines of the output file, what you would like it to look like, so I can understand your goals in this dataflow?

[jeff_k]

I think it would be helpful to see an image of the overall dataflow, as well as the specific transform where the row count is being done. Are you trying to get an overall row count or # rows per group in your aggregation?

Regards,

Jeff

[former_member423067]

Hello Jeff,

As per help page, row count increments value by 1 every time the function is called. how to make sure, the trailer segment be called as the last instance in Transform.

This will ensure, the record count to occur correctly.