topic Re: Splitting Data in QlikView

Splitting Data

umartareen — Tue, 23 Sep 2014 12:05:20 GMT

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

Re: Splitting Data

richard_pearce6 — Tue, 23 Sep 2014 12:08:24 GMT

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

Re: Splitting Data

maxgro — Tue, 23 Sep 2014 12:09:30 GMT

t3:

load * inline [

Amount

100

200

-100

];

final:

load

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

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

Resident

t3;

Re: Splitting Data

ashfaq_haseeb — Tue, 23 Sep 2014 12:09:32 GMT

HI,

Try below

If(Amount<0,Amount) as DebitAmount

If(Amount>0,Amount) as CreditAmount

Regards

ASHFAQ

Re: Splitting Data

senarath — Tue, 23 Sep 2014 12:11:56 GMT

Hi,

Try like below.

load

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

if(CostField<0,CostField) as NegCost,

From

thanx

Re: Splitting Data

simondachstr — Tue, 23 Sep 2014 12:16:06 GMT

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

Re: Splitting Data

umartareen — Tue, 23 Sep 2014 12:16:16 GMT

Ok perfect Richard and Ashfaq,

Thank You

Re: Splitting Data

umartareen — Tue, 23 Sep 2014 12:18:06 GMT

Thanks Martin.

Re: Splitting Data

Anonymous — Tue, 23 Sep 2014 12:24:10 GMT

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 ;