topic Re: How to get only one value in one column in QlikView

How to get only one value in one column

pra_kale — Tue, 28 Jun 2016 16:55:58 GMT

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

Re: How to get only one value in one column

sinanozdemir — Tue, 28 Jun 2016 17:03:25 GMT

Hi,

Please see below:

I used the below expression in a straight table:

Hope this helps.

Thanks

Re: How to get only one value in one column

trdandamudi — Tue, 28 Jun 2016 17:09:24 GMT

You can also use the below expression for Sum_Assured:

Aggr(Max(Sum_Assured), PolicyNumber)

Re: How to get only one value in one column

sunny_talwar — Tue, 28 Jun 2016 17:19:36 GMT

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

Re: How to get only one value in one column

pra_kale — Tue, 28 Jun 2016 17:45:41 GMT

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.

Re: How to get only one value in one column

sunny_talwar — Tue, 28 Jun 2016 18:10:57 GMT

Try this:

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;

Re: How to get only one value in one column

pra_kale — Wed, 29 Jun 2016 16:45:30 GMT

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.