Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
umartareen
Creator II
Creator II

Splitting Data

Hi all,

how can i split data in the edit script window ? i have Amount in negative and positive values. I want the negative values as a separate field DebitAmount and the positive values as another field called CreditAmount.

Thanks in advance

Umar

1 Solution

Accepted Solutions
ashfaq_haseeb
Champion III
Champion III

HI,

Try below

If(Amount<0,Amount) as DebitAmount

If(Amount>0,Amount) as CreditAmount

Regards

ASHFAQ

View solution in original post

8 Replies
richard_pearce6
Luminary Alumni
Luminary Alumni

In the load use a conditional statement.

,If(Amount<0,Amount,Null() )      as NegativeAmount

,If(Amount>0,Amount,Null() )      as PossitiveAmount


regards

Richard

Qlikcentral.com

maxgro
MVP
MVP

t3:

load * inline [

Amount

100

200

-100

0

];

final:

load

  if(Amount>=0, Amount, 0) as CreditAmount,

  if(Amount<0, Amount, 0) as DebitAmount

Resident

  t3;

ashfaq_haseeb
Champion III
Champion III

HI,

Try below

If(Amount<0,Amount) as DebitAmount

If(Amount>0,Amount) as CreditAmount

Regards

ASHFAQ

senarath
Creator III
Creator III

Hi,

Try like below.

load

if(CostField>=0,CostField) as PlusCost,

if(CostField<0,CostField) as NegCost,

From

thanx

simondachstr
Luminary Alumni
Luminary Alumni

There's a special function called sign() for that purpose. Might be faster using that instead.

Try below

if(sign(Amount)=1,Amount)      AS      CreditAmount

if(sign(Amount)=-1,Amount)     AS      DebitAmoutn

umartareen
Creator II
Creator II
Author

Ok perfect Richard and Ashfaq,

Thank You

umartareen
Creator II
Creator II
Author

Thanks Martin.

Anonymous
Not applicable

Hi ,

Please follow the below code

Load * Inline [
Id,Amount
1,100
2,200

3,-100
4,-300
]
;

Final:
Load
If(Amount >= 0,Amount,0) as CreditAmount,
If(Amount <= 0, Amount,0) as DebitAmount
Resident TestData ;