Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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];
For second row-> Number of lines 2; Line associated - 41. How do you get 85?
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)?
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
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];