Hierarchical Regroup - Multiple children-on the right track?

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
SettValleyConsulting
Premium Member
Premium Member
Posts: 72
Joined: Thu Sep 04, 2003 5:01 am
Location: UK & Europe

Hierarchical Regroup - Multiple children-on the right track?

Post by SettValleyConsulting »

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??
Phil Clarke
SettValleyConsulting
Premium Member
Premium Member
Posts: 72
Joined: Thu Sep 04, 2003 5:01 am
Location: UK & Europe

Post by SettValleyConsulting »

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?
Phil Clarke
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

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.
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

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
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
SettValleyConsulting
Premium Member
Premium Member
Posts: 72
Joined: Thu Sep 04, 2003 5:01 am
Location: UK & Europe

Post by SettValleyConsulting »

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.
Phil Clarke
misterben
Premium Member
Premium Member
Posts: 1
Joined: Wed Dec 13, 2017 6:33 am
Location: France

Post by misterben »

Hello,

In the end, how did you address your problem ? I'm facing the same situation, and waiting for my Premium account activation.
Thanks
Tout ce qui mérite d'être fait mérite d'être bien fait !
Post Reply