DSXchange

Hierarchical Regroup - Multiple children - Am I on the right track?

Problem: I have a flat record coming in, representing a motor insurance quote. The quote may have multiple drivers, and each driver may have multiple claims and multiple convictions. So a tree structure with quote at the top, then driver, then claim and conviction both as a child of driver. I've done the joins upstream such that there will be one row for each combination of quote+driver+claim+conviction (that is, a quote with 2 drivers each with 1 claim and 1 conviction would generate 4 rows.)

This must be composed into an XML record of the same structure. So my intention is to regroup first on driver then on claim and conviction using the output of the Driver regroup as the input list.

Each driver has a unique number which I used as my key when regrouping drivers. In the hierarchical stage when selecting parent and child fields the key (Driver Number) goes in the parent list (Quote). Therefore when coming to regrouping claims and convictions using the result of the Driver regroup, the driver number is not available to use as a key.

This seems a bit of a design flaw. My intended workaround is to duplicate the Driver number field (called something like ConClaimDriverNumber) and include it in the child list at the Driver Regroup stage. I haven't tried this yet.

I'm a newbie with the hierarchical stage but this seems a bit counterintuitive; is there a more elegant solution? In other words, am I on the right track??

Update: So I've tried that method, however when trying to map the claim and conviction children I am getting the dreaded CDIUI2820E 'The mapping is not applicable due to an invalid type conversion or difference in the source and target list dimensions.'

Back to the drawing board?

Yes, you are right on the spot...add the artificial keys upstream for each list that would be used in the regroup steps, when doing the join...
This is by design, in case you work with multiple independent sources "List" as input you would need to rely on the key within each list to be able to create the hierarchy.

Based on this thread, it looks like Conviction and Claim are two independent lists......you probably went thru some interesting gyrations to get them onto the same input link, but in fact, it probably would make more sense for them to be on their own independent input links...one for conviction and one for claims.....

...then each will get their own set of regroups "down" to that point.....and then an HJoin that will bring them together under the same parent....

Not sure, but that could be what is thwarting you here.......

Ernie

Thanks, Ernie.

Yes, Claim and Conviction are independent children of Driver, and they are joined upstream.

As I said, I am a Hierarchical stage newbie, and HJoin was on my list of the next things to try. But surely this should be achievable using Regroup?

I have to observe, however that the I am finding the way the Hierarchical Data Stage works far from intuitive and the documentation and examples pretty woeful.

Hello,

In the end, how did you address your problem ? I'm facing the same situation, and waiting for my Premium account activation.
Thanks