topic Re: Eliminate some data in QlikView

Eliminate some data

shamsu_mk — Wed, 25 Nov 2020 16:16:04 GMT

Hello team

Please find attached an excel table. I would like to have a Qlickview report by eliminating some data as per the below conditions. The items to be eliminated has been marked with yellow color

The condition for elimination is followed.

Under one employee code,

If sum of WT=3000 and WT = 3005 is zero , the both line which represents 3000 and 3005 has to be eliminated,

if not zero some color has to be marked show the unmatched item

if there is only one WT ( 3000 or 3005 ) no action required

Sorry, if my requirement confuses you, but please support me.

Re: Eliminate some data

pooja_prabhu_n — Wed, 18 Jul 2018 06:52:19 GMT

Try this

=if([Wages Type]=3000 or [Wages Type]=3005,

if((Sum( Aggr(Sum({<[Wages Type]={'3000'}>}[Amount 2]),Employee))+Sum( Aggr(Sum({<[Wages Type]={'3005'}>}[Amount 2]),Employee)))>0,1,0),1)

Re: Eliminate some data

mtucholski — Wed, 18 Jul 2018 07:14:27 GMT

If any explanation is needed, please let me know. You just have to replace file path.

//Load all the data

ReportTemp:

LOAD [Posting Key],

Amount,

Employee,

[Wages Type],

[Amount 2],

if([Wages Type]=3000 or [Wages Type]=3005,Employee&'-X',0) as mark

FROM

(ooxml, embedded labels, table is Arkusz1);

//sum of '3000' and '3005'

left join (ReportTemp) load

sum([Amount 2]) as am2,

Employee&'-X' as mark

resident ReportTemp

where [Wages Type]=3000 or [Wages Type]=3005

group by Employee&'-X'

;

//create report from previous table where sum of 3000 and 3005 is >0 or where there is no 3000 or 3005 in line (it's sum is null)

Report:

NoConcatenate load

[Posting Key],

Amount,

Employee,

[Wages Type],

[Amount 2],

mark

Resident ReportTemp

where isnull(am2)=-1 or am2>0

;

Drop Table ReportTemp

Re: Eliminate some data

shamsu_mk — Wed, 18 Jul 2018 07:27:26 GMT

You mean in expression?

Re: Eliminate some data

pooja_prabhu_n — Wed, 18 Jul 2018 07:52:23 GMT

Yes

Re: Eliminate some data

shamsu_mk — Wed, 18 Jul 2018 12:22:34 GMT

There is a small issue. If the WT 3000 + WT 3005 = 0, The result is coming properly as I needed. But If there is only one WT available then it is not zero, but it is showing as zero as per your formula. See below example. In this case, the result should not be zero, whereas it is appearing as zero as the formula.

Re: Eliminate some data

pooja_prabhu_n — Thu, 19 Jul 2018 05:36:46 GMT

Hi,

Use "<>0" instead of >0 in the expression,if the sum is negative it was considering it as 0 according to expression.

if([Wages Type]=3000 or [Wages Type]=3005,
if((Sum( Aggr(Sum({<[Wages Type]={'3000'}>}[Amount 2]),Employee))+Sum( Aggr(Sum({<[Wages Type]={'3005'}>}[Amount 2]),Employee)))<>0,1,0),1)

Thanks,

Pooja

Re: Eliminate some data

shamsu_mk — Thu, 19 Jul 2018 06:35:01 GMT

Still not ok,

Please find enclosed QV report after the updation. You can see the green marked items are fine. This is correct, but see the red marked, there is only one WT which is 3005 and it should not be zero, it can be 2 in the filter column.

If Sum of WT = 0 , then 0 , If there is only one WT(Either 3000 or 3005) then 2, otherwise 1 in a particular employee code.

Re: Eliminate some data

shamsu_mk — Thu, 19 Jul 2018 06:35:57 GMT

Thank you. I will try this also and update you the status.

Re: Eliminate some data

mtucholski — Thu, 19 Jul 2018 06:52:29 GMT

There should be "where isnull(am2)=-1 or am2<>0" in the last line, not "where isnull(am2)=-1 or am2>0". Sorry for that.

Re: Eliminate some data

shamsu_mk — Thu, 19 Jul 2018 09:52:31 GMT

As marked in red some amounts are doublling in the report.

Re: Eliminate some data

mtucholski — Fri, 20 Jul 2018 05:43:34 GMT

Hi,

I have loaded your data from .xlsx and put the code I wrote as a only load script. The result is as below. So I think it's something else that doubles the rows. You have to look through your load script. Note that my script loads only '0' marks.