How to get only one value in one column

[pra_kale]

Hi,

I have attached one sample application in that SumAssured is repeated. How i can get output as per below given format. Means only one SumAssured in front of PolicyNumber.

Please help..

Thanks in Advance.

PolicyNumber	Lives	Premium	Sum_Assured
123456	A123	5000	500000
123456	A122	23000
123456	A124	45000
461234	B123	4500	100000
461234	B132	1234
32456	AA12	56000	12000
654133	BA11	32000	90000
654133	BA12	65000

[sinanozdemir]

Hi,

Please see below:

I used the below expression in a straight table:

Hope this helps.

Thanks

[trdandamudi]

You can also use the below expression for Sum_Assured:

Aggr(Max(Sum_Assured), PolicyNumber)

[sunny_talwar]

You can further make null symbol to be empty space, in case you don't want to see '-'. This is just for enhancement purposes, but I think sinanozdemir‌ and trdandamudi‌ have banged your requirement

[pra_kale]

Thanks Sunny,Sinan and Thirumala for your help..

But i want same thing to be done through script, as i am going to use this data for further calculation and data size is also large so through script only i am going to export the final output in .csv file.

Whether it is possible...

Thanks in advance.

[sunny_talwar · Accepted solution]

Try this:

A:

LOAD PolicyNumber,

    Lives,

    Premium,

    Sum_Assured

FROM

[Policy_SA.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalA:

NoConcatenate

LOAD PolicyNumber,

    Lives,

    Premium,

    If(PolicyNumber <> Previous(PolicyNumber) and Lives <> Previous(Lives), Sum_Assured) as Sum_Assured

Resident A

Order By PolicyNumber, Lives;

DROP Table A;

[pra_kale]

Thanks Sunny...It is working.

Just want to ask one thing, the way you did in script If(PolicyNumber <> Previous(PolicyNumber) and Lives <> Previous(Lives). But, if i am having more than 15 or 20 columns then whether i have to mentioned each and every column the way you did  or any other option is available.

Thanks once again.