topic Aggregate count on parent and child both in QlikView

Aggregate count on parent and child both

Anonymous — Tue, 11 Mar 2014 14:43:30 GMT

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	Y
C	C1	N	N
C	C2	N	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!

Re: Aggregate count on parent and child both

datanibbler — Tue, 11 Mar 2014 14:49:38 GMT

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

Re: Aggregate count on parent and child both

MK_QSL — Tue, 11 Mar 2014 14:50:05 GMT

Can you be more specific in your requirements?

If possible, please explain with required result.

Re: Aggregate count on parent and child both

jagannalla — Tue, 11 Mar 2014 14:55:04 GMT

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

Re: Aggregate count on parent and child both

Anonymous — Tue, 11 Mar 2014 15:04:38 GMT

Thanks, Jagan!

It worked how i wanted.