Logic implementation clarification

[Anonymous]

Hi Everyone,

I have a client requirement they have a data  Order number, GRR number , Number of lines and Line associated wise amt as in below table. Requirement is if Number of lines data exist in Line associated  data then sum of those data wise(Line associated) amt should be appear in  the row of Number of lines.
For Example :   GRR Number = 15000651/OV/15000  have Number of lines 1 and this is existing in line associated column 3 times  Client require  1139 + 806+684 = 2629 should be appear in the row of same GRR Number as in Expected Column similarly for all GRR number.

Order Numer	GRR Number	Number of Lines	Line - Associated	Amt	Expected
14000184/O1/10000	15000651/OV/15000	1	3	18,491	2629
14000184/O1/10000	15000652/OV/15000	2	41	18,481	85
14000184/O1/10000	15000665/OV/15000	6	1	1,139	360
14000184/O1/10000	15000665/OV/15000	9	1	806	53
14000184/O1/10000	15000665/OV/15000	10	1	684	185
14000184/O1/10000	15000665/OV/15000	11	2	82
14000184/O1/10000	15000665/OV/15000	12	2	2
14000184/O1/10000	15000665/OV/15000	13	2	1
14000184/O1/10000	15000665/OV/15000	16	6	34
14000184/O1/10000	15000665/OV/15000	17	6	307
14000184/O1/10000	15000665/OV/15000	23	6	16
14000184/O1/10000	15000665/OV/15000	24	6	2
14000184/O1/10000	15000665/OV/15000	25	9	0
14000184/O1/10000	15000665/OV/15000	26	9	0
14000184/O1/10000	15000665/OV/15000	27	9	47
14000184/O1/10000	15000665/OV/15000	28	9	6
14000184/O1/10000	15000665/OV/15000	29	10	0
14000184/O1/10000	15000665/OV/15000	30	10	0
14000184/O1/10000	15000666/OV/15000	3	10	185	18491

Please anyone guide me if it is possible.  Looking forward your suggestion .

For assistance find the data in attachment.

[tresesco]

For second row-> Number of lines 2; Line associated - 41. How do you get 85?

[jonathandienst]

But the GRR numbers for those three is different to the first line. So do we ignore GRR number for this calculation?

And by your explanation, shouldn't line 3 be 359 (not 360)?

[Anonymous]

Hi Tresco,

Thanks for your quick response, for second row - > Number of lines 2 :  is existing in Line Associated column 3 times so the sum of amt corresponding to that associated lines is 85.

see in image

[Kushal_Chawda · Accepted solution]

Data:

LOAD [Order Numer],

    [GRR Number],

    [Number of Lines],

    [Line - Associated],

    num(Amt) as Amt

FROM

(biff, embedded labels, table is Sheet1$);

Link:

LOAD Distinct [Line - Associated] as [Number of Lines],

sum(Amt) as ExpectedAmt

Resident Data

Group By [Line - Associated];