Splitting Data

[umartareen]

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

[richard_pearce6]

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]

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 · Accepted solution]

HI,

Try below

If(Amount<0,Amount) as DebitAmount

If(Amount>0,Amount) as CreditAmount

Regards

ASHFAQ

[senarath]

Hi,

Try like below.

load

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

if(CostField<0,CostField) as NegCost,

From

thanx

[simondachstr]

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]

Ok perfect Richard and Ashfaq,

Thank You

[umartareen]

Thanks Martin.

[Anonymous]

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 ;