Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Eliminate some data

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.

11 Replies
Highlighted
Creator III
Creator III

Re: Eliminate some data

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)

Highlighted
Creator
Creator

Re: Eliminate some data

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

Highlighted
Creator
Creator

Re: Eliminate some data

You mean in expression?

Highlighted
Creator III
Creator III

Re: Eliminate some data

Yes

Highlighted
Creator
Creator

Re: Eliminate some data

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.

Capture.JPG

Highlighted
Creator III
Creator III

Re: Eliminate some data

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

Highlighted
Creator
Creator

Re: Eliminate some data

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(EithCapture.JPGer 3000 or 3005) then 2, otherwise 1 in a particular employee code.

Highlighted
Creator
Creator

Re: Eliminate some data

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

Highlighted
Creator
Creator

Re: Eliminate some data

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.