topic Re: How to Avoid repeated values in Straight Table, or merging the values based on one column in QlikView

How to Avoid repeated values in Straight Table, or merging the values based on one column

Anonymous — Tue, 10 Apr 2018 10:54:32 GMT

I have straight tables which have 10 more columns, Column CLAIMS & EXPENSES have value for each Part Number.

But i need to show only one value not to repeat on next row. Due to this while export and check the sum in Excel Sum values doubles.

If there is any way to avoid this? Is there any merge option for this type of problem.

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

avinashelite — Wed, 11 Apr 2018 07:18:43 GMT

Why din't you use the pivot then ??

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

stabben23 — Wed, 11 Apr 2018 07:22:29 GMT

They are not duplicates, My Out have 2 different values, 2018,2019

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

avinashelite — Wed, 11 Apr 2018 07:32:17 GMT

As stabben23‌ commented you have an dimension to distinguish those value and the way your looking at the data is wrong ...they are to different values all together

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

Anonymous — Wed, 11 Apr 2018 08:04:39 GMT

Hi Ramkumar,

By looking at your screenshot, I can see "My Out" is an expression.

If your "My Out" is used as a dimension rather than an expression, then you can avoid these duplicate values. Only then, your claims and expenses expressions will show different values for 2018 and 2019.

Do try it and let me know if this worked out for you?

Regards,

Benazir

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

stabben23 — Wed, 11 Apr 2018 08:27:34 GMT

I'll think you are wrong here, MY OUT is definitely a Dimension and also the cause to "duplicated" rows.

If he romoves this Dimension, he will probably get one line, but I think he rather should have a look into his datamodel.

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

Anonymous — Wed, 11 Apr 2018 08:40:26 GMT

Hi Staffan,

Maybe I'm wrong but please help me out: I looked at the screenshot and saw a "searchable" on that column(the small magnifying glass). When I create a straight table, I can only have dropdowns on my dimensions while the "searchable" option is available only for the expressions.

This sort of lead me to assume he has "My Out" as an expression. Is there a way to enable "searchable" for dimensions as well?

Rgds,

Benazir

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

Anonymous — Wed, 11 Apr 2018 08:44:15 GMT

Yes Staffan, you are right.

I am using MY OUT has Dimension. Claims & Expenses are measure value. I understand due to 2018 & 2019 MY OUT its shows another value. But my concerns the same, i need one unique value in claims & expenses, because Claim & Expenses are sum value of Part Number. All other measure value correctly showed based on Dimension of PartNumber, MY IN MY OUT & SUPPILER CODE etc...

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

avinashelite — Wed, 11 Apr 2018 08:46:09 GMT

then remove the Year dimension !!! or try to get the logic which year you want to display the values E.G for the year 2018 or 2019

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

Anonymous — Wed, 11 Apr 2018 08:48:48 GMT

Yes if i remove Year Dimension, values are not duplicate...

But i need Year too, so that we see RW QTY, RW $ data's based on Model Year IN & OUT.

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

avinashelite — Wed, 11 Apr 2018 08:58:56 GMT

hmm could you show us the sample output your excepting ?? because if the same value is associated with 2 different year then it will obviously it will come as 2 different rows and in the excel it will sum up .....In this case you need to apply the year filter and analysis the sum other wise it will be an issue

instead of adding the year as an dimension ...build 2 expression for each year that will solve the issue

Hope this helps

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

stabben23 — Wed, 11 Apr 2018 09:00:24 GMT

ok, my first Guess is that you have concatenate two (or more) fact tables With a datefield. One of those contain year 2019.

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

Anonymous — Wed, 11 Apr 2018 09:25:01 GMT

Here i attached sample output, as well as excepting table also shown in excel.. !

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

stabben23 — Wed, 11 Apr 2018 09:51:10 GMT

Ok, let me Guess again, Expression (Values) EXPENSES and CLAIMS is from one fact table, the other Expression is from another. This is probably concateneted in script. when concatenate tables, all Dimension Field thats should be used in Your straight table should be common in both fact tables, if not, you will see this behaviour.

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

Anonymous — Wed, 11 Apr 2018 09:56:17 GMT

Yes Correct Staffan.. I am using two different data sets in back ground, which i am using straight tables here in the APP, which join based on Part Number.

Is there any method to avoid this behaviour? Otherwise any merge option works?

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

stabben23 — Wed, 11 Apr 2018 10:09:32 GMT

ok, a join is used, it would be useful to see what Field who is in the join table. Could it be that MAJOR, MINOR and MY OUT is in the joined table?

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

Anonymous — Wed, 11 Apr 2018 10:12:08 GMT

No i am using only Part Number dimension to join both the tables. Because Part number is Primary key in both tables.

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

stabben23 — Wed, 11 Apr 2018 10:21:35 GMT

Could you show us some sample app or screenshot from script? will be useful.

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

m_s — Wed, 11 Apr 2018 11:44:02 GMT

Hello Ramkumar,

have you tried setting the text color to white if the expression's value is the same as a row above?

I used the following expression for text color: if (above(TOTAL [CLAIMS])=[CLAIMS], White(), Black())

Cheers,

Mathias

Re: How to Avoid repeated values in Straight Table, or merging the values based on one column

stabben23 — Wed, 11 Apr 2018 11:53:53 GMT

So when export to Excel, the textcolor wont affect sum in a row?! Are u serious?