topic Re: To avoid duplicate amount & multiple time repeating in QlikView

To avoid duplicate amount & multiple time repeating

dhanavindhan — Wed, 25 Apr 2018 10:44:35 GMT

Hi All,

I have a table which is filtered by my selection criteria for compression purpose.

1.) I have a button "show All" which is used to filter the record based on my selection criteria. Let's take my selection was "DAL" it need to show me DAL Charge amount only. If there is no DAL charge amount then it should be left blank or "-". But you see in my above screenshot i am getting some charge amount which are not having DAL charge amount i need to show them blank.

2.) Same SRR No which has DAL charge repeating again. I need to avoid them too.

Below attached my qvw. Please help me to fix this issue.

Re: To avoid duplicate amount & multiple time repeating

sunny_talwar — Wed, 25 Apr 2018 11:35:47 GMT

Checking

Re: To avoid duplicate amount & multiple time repeating

sunny_talwar — Wed, 25 Apr 2018 11:41:59 GMT

Change your third last dimension to this

=num(If(Len(Trim(Aggr(Only({1<SRRReport.Charge = P(SRRReport.Charge)>}SRRReport.Charge), SRRReport.Charge, SRRReport.usdamt))) > 0,

Aggr(Only({1<SRRReport.Charge = P(SRRReport.Charge)>}SRRReport.usdamt), SRRReport.usdamt)),'$#,##0.00')

This def. resolves your 1st requirement, not sure I understand the second one.... is that resolved also? I don't see the second line anymore, but it could be sorted differently

Re: To avoid duplicate amount & multiple time repeating

dhanavindhan — Wed, 25 Apr 2018 12:36:46 GMT

Hi Sunny,

Thanx for the quick reply.

You are correct 1'st one is working perfect and good. I am still getting those SRR no are repeating again it's not sorted out. Please see my screenshot.

Re: To avoid duplicate amount & multiple time repeating

sunny_talwar — Wed, 25 Apr 2018 12:41:02 GMT

I am not seeing in my version of the app...

All my selections are this

What am I doing wrong? Check the attached app to see if you are seeing something different

Re: To avoid duplicate amount & multiple time repeating

bc-thebruuu — Wed, 25 Apr 2018 12:44:02 GMT

Why do you get rid off the Charge Id selection in CTB/teu Expression?

Re: To avoid duplicate amount & multiple time repeating

dhanavindhan — Wed, 25 Apr 2018 14:58:32 GMT

Hi Sunny,

As mentioned above user need for comparison. Please apply this selection criteria and you will see that repeating issue.

Selection criteria:

Year - 2018

Month - 4

SRRSelection - Y

ChargeID - DAL

after applying this selection Click on "Show all " Button. Don't apply selection criteria in Delivery & Origin. Now, It will show DAL charge amount and again it will be repeated in the next line without chargeID & Charge amount which I highlighted in my above screen shot. I want to get rid of that empty one. Don't know why it is coming bring it empty in next line.

Regards,

Dhana

Re: To avoid duplicate amount & multiple time repeating

sunny_talwar — Wed, 25 Apr 2018 15:08:29 GMT

Can you re-run the attached dashboard where I have added a new field within SRRReport Table and post it back...

AutoNumber(Origin&Delivery&CustomerName&srrno&eqptype&validFrom&validto&ExpFrDays&ImpFrDays&AllinRate) as key,

Re: To avoid duplicate amount & multiple time repeating

dhanavindhan — Wed, 25 Apr 2018 15:17:21 GMT

Sunny, I know it is silly you mean re-run (reload) the file is that correct?

Re: To avoid duplicate amount & multiple time repeating

sunny_talwar — Wed, 25 Apr 2018 15:18:21 GMT

Yup

Re: To avoid duplicate amount & multiple time repeating

dhanavindhan — Wed, 25 Apr 2018 15:28:12 GMT

Holly Mother of all god....I didn't noticed it. Becoz, I was writing SP for another client. It's my mistake man. Thanks a lot bro Will check and let you know.

Re: To avoid duplicate amount & multiple time repeating

sunny_talwar — Wed, 25 Apr 2018 15:30:35 GMT

Holly Mother of all god....I didn't noticed it. Becoz, I was writing SP for another client. It's my mistake man. Thanks a lot bro Will check and let you know.

I am not sure I follow....

Re: To avoid duplicate amount & multiple time repeating

dhanavindhan — Thu, 26 Apr 2018 06:07:00 GMT

Hi Sunny,

The issue still persist. The key which u created is showing all the sub-charges (ChargeID) it is not needed in the report. The previous qvw moreover 85% correct. No need to bring key field here.

Example 1:

As per my selection criteria it is DAL so it should Show in the report SRR No's which are having DAL and it's Chargeamount & SRR No's which are not applicable for DAL (Here I wanted to show those ChargeID & Charge amount as empty). So the user easily came to know SRR no's which are not applied DAL charge and they will look into that particular SRR No Details Why DAL is not applied for that.

Example 2: Please refer my screenshot

In my above screenshot i highlighted in 2 colors I want only Green record in the report. The red one was don't know why it is repeating again without Charge ID and Charge amount i need to scrap this red column from my report. If you notice the SRR no, Customer name, Origin, delivery, equp.type etc..are all showing same. Please refer my current selection box. I am getting this when i apply this selection criteria.

Key field QVW:

This one is wrong. If u create Key filed it will expand all the ChargeID under that particular SRR number and show them all in the record. I need to show only charge ID which i selected. DAL means it need to show DAL applicable SRR no and not applicable srr no's.

Regards,

Dhana R

Re: To avoid duplicate amount & multiple time repeating

dhanavindhan — Thu, 26 Apr 2018 08:48:12 GMT

Hi Sunny,

The duplicate is still coming even after reloading it. Attached reloaded file. Pls check.

Regards,

Dhana

Re: To avoid duplicate amount & multiple time repeating

sunny_talwar — Thu, 26 Apr 2018 08:53:03 GMT

I did not say that adding the key will resolve the problem of duplicates... all I said was reload the app and share it back here....

Re: To avoid duplicate amount & multiple time repeating

dhanavindhan — Thu, 26 Apr 2018 10:24:19 GMT

Hi Sunny,

I attached the reloaded file. Please check my above comments.

Regards,

Dhana R

Re: To avoid duplicate amount & multiple time repeating

sunny_talwar — Thu, 26 Apr 2018 12:21:41 GMT

Check the SRR Report (New) chart

Re: To avoid duplicate amount & multiple time repeating

dhanavindhan — Thu, 26 Apr 2018 15:12:47 GMT

Hi Sunny,

It's working fine bro...you are awesome and freaking genius man.... So u created the key field to call in Expression so Auto Number will set Uniqueness for the record and remove the repeating value, is that corrct?

Re: To avoid duplicate amount & multiple time repeating

sunny_talwar — Thu, 26 Apr 2018 15:14:34 GMT

Sort off.... but I think this could still work without needing to create the key field...