Eliminate some data

[shamsu_mk]

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.

[pooja_prabhu_n]

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)

[mtucholski]

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

[shamsu_mk]

You mean in expression?

[pooja_prabhu_n]

Yes

[shamsu_mk]

Hi

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.

[pooja_prabhu_n]

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

[shamsu_mk]

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.

[shamsu_mk]

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

[mtucholski]

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.