How to show distinct value

[posywang]

Hi,

We had a data glitch from source file so it created two resource names with same values for other columns. It was not the first time the source system did that and we had no control over it so that's why I didn't go with the approach to just manually remove the wrong resource name, in case such issue happens again in the future. I am wondering if I can use set analysis or something to avoid data duplication in Qlikview. Attached is a simple sample file. Is it possible to show Headcount just once? I don't care which resource name shows up as long as there's only one row in table...

Current View:

Preferred View:

Thank you in advance. Any idea is appreciated!

[vishsaggi]

There are different ways we can reduce data. You want this in UI or Script level? And you have just provided two rows of data in your excel sheet? What are the other ways data will come from your FTE table and how you identify that they are duplicates? Are you going to use Key as your identifier here? your FTE values says 0 and 0.8 is this field not used?

One way you can reduce is like below Script:

FTE:
LOAD *
WHERE KeyFlag = 1;
LOAD IF(Key = Previous(Key), 1, 0) AS KeyFlag,
Resource,
ShortName,
Code,
FTE,
Key
FROM
[Test file shortname.xlsx]
(ooxml, embedded labels, table is Sheet1);

  Headcount:
LOAD Key,
Headcount
FROM
[Test file shortname.xlsx]
(ooxml, embedded labels, table is Sheet2);

[posywang]

Is it possible to have it at UI level? The example is a simplified version of data we have and the real model is much more complicated and hard to update... the reality is that we archive data for 30 days and the wrong resource name got replaced with right name after a couple of days but it remains in dataset until after 30 days... FTE value back then was 0 and then it got updated to 0.8 that's why there were two values...

[tamilarasu]

Hi Xia,

Have a look at the attached file.

[posywang]

Thank you Tamil. It is creative to do it that way! However, sometimes we have two values both to be zero... I know this is a weird scenario I posted here...

[tamilarasu]

Xia- I knew you will come back .  Have a look at the new attachment and let me know if this is ok or not.

Xia Wang wrote:

I don't care which resource name shows up as long as there's only one row in table...

My solution is based on you above point.

[posywang]

Haha, sorry about the misleading comments. Unfortunately I don't think the 2nd solution you suggested would work either because there are many resources than just these two in simplified example. Also, 0.8 didn't show in FTE...

[vishsaggi]

You need multiple combination scenarios to be considered here if you do not want this in backend. Can you give us more possible data or information to help you. ?

[tamilarasu]

No worries, Xia . I assume you have FTE values like below snapshot and based on that I have attached my suggestion. If this is not working, we need more sample data.

Note: I am showing the resource name which is having highest FTE value.

[posywang]

Thank you Tamil. Your solution worked great for the scenario that I provided. However, once I try to use the same method to my real chart, it no longer works and I've tried tweaking it for whole day today. So I have to create another QVW which is more complicated than the first example that I provided but it is closer to our complicated data model. As I mentioned, we keep 30 days of forecast in our dataset but there's some version like budget which never changes so instead of loading it 30 times, we only load it once with "NTS" as date. Our application also defaults to the most recent date to show the most recent forecast. That's why the bad name doesn't show up in other reports because bad resource name happened a while back (still within 30 days) and no longer there when we select the most recent date. The issue is that the report that I am working on has budget in expression, which shows both good and bad names because most recent date is no longer valid in this case ("NTS" took over in this case). Hope my explanation makes sense. I attach a new example, please take a look and let me know if this is something fixable in chart. Thanks again!