topic Straight table duplicates in QlikView

Straight table duplicates

manas_bn — Thu, 09 Aug 2018 05:12:06 GMT

Hi all,

I have a custom report where users can select dimensions and measures to bring up a straight table.

That table has data something like the below:

Row Number, No of Seats, Count of PCs

1, 0, 20

1, 10, 20

1, 0, 20

2, 0, 10

3, 10, 10

4, 10, 20

4, 0, 10

5, 10, 20

6, 0, 10

I need to filter out the duplicate row numbers so that only the record with non-zero seats remains. For row numbers that are not duplicated, I don't want to filter anything.

Ex: Row number 1 has 3 records, but I need only the 2nd row which has a seat count. For Row Number 4, I need the 1st row with seat count 10.

So in the above data set, I need the following:

Row Number, No of Seats, Count of PCs

1, 10, 20

2, 0, 10

3, 10, 10

4, 10, 20

5, 10, 20

6, 0, 10

Thanks in advance!

Manas

Re: Straight table duplicates

mtucholski — Thu, 09 Aug 2018 06:43:43 GMT

Hello,

I'm not sure If understood you correctly.

Try to add calculated dimension like that:

=if(aggr(sum([No of Seats]),[Row Number])=[No of Seats],[Row Number],if([No of Seats]=0,null(),[Row Number]))

and suppres null values for this dimension.

Check the example.

Re: Straight table duplicates

Anil_Babu_Samineni — Thu, 09 Aug 2018 09:10:05 GMT

May be create straight table using below?

Dimension -- [Row Number]

Expression

1) Sum([No of Seats])

2) Sum({<[No of Seats] = {"=Sum([No of Seats])>0"}>+<[No of Seats] -= {"=Sum([No of Seats])>0"}>} [Count of PCs])