Aggregate count on parent and child both

[Anonymous]

Hi All,

I have a requirement where i need to count the number of FLAGs (Y or N) for Product and product line levels. One product can have multiple product lines and that one product can contain some product line as 'Y' and some as 'N'. But if my product has a single 'N' for any product line, then overall it would be 'N'. Please find below example for clear idea.

Product	ProductLine	FlagForProductLine	FlagForProduct
A	A1	Y	N
A	A2	N
A	A3	Y
B	B1	Y	Y
B	B2	Y
C	C1	N	N
C	C2	N

I have product,productline and FlagForProductLine information in my data. Data is at productline level in database.

Can anyone please help me in calculating FlagForProduct?

Thanks in advance!

[datanibbler]

Hi Subhasmita,

in effect, you have to concatenate all your "status_letters", so you get sth. like NYYN or YYYN or whatever - and then you can use a string_function like INDEX() to find out if that string contains at least one 'N', in which case your overall "status" would be N.

=> On scripting level, you would have to use the PEEK() fct., to check whether the product is still the same as in the record immediately above and

- if that is the case, concatenate the status_letter to the construct you already have

- if not, just write this record's status_letter as a new field (start a new construct)

<=> You also have to allow for the very first record where the PEEK() function will not work as there is no record above. You can catch that using the Recno() function.

HTH

Best regards,

DataNibbler

[MK_QSL]

Can you be more specific in your requirements?

If possible, please explain with required result.

[jagannalla · Accepted solution]

Hi,

Try this,

T1:

LOAD * Inline [

Product, ProductLine,FlagForProductLine

A, A1, Y

A, A2, N

A, A3, Y

B, B1, Y

B, B2, Y

C, C1, N

C, C2, N

];

Join

LOAD Product, If(SubStringCount(Concat(FlagForProductLine),'N')>0,'N','Y')  as FlagForProduct  Resident T1 Group By Product;

Thanks,

Jagan

[Anonymous]

Thanks, Jagan!

It worked how i wanted.