How to separate + and - numbers from a numeric field?

[Anonymous]

I have a numerical field (EXP_AMT) that contains positive and negative numbers.

I want to be able to script the positive numbers from that field into an (EE_Spend) field and negative into (EE_Credit) field, still retaining the original (Exp_Amt)

Please see attachment

Your assistance would be gratefully received..

[MK9885 · Accepted solution]

Maybe use below in your script backend

if(EXP_AMT<0,EE_Credits) as EECREDITS,

if(EXP_AMT>0,EE_Credits) as EESPEND

You can drop original fields if required at the end of the load.

Test:

LOAD EXP_AMT,

     EE_Spend,

     EE_Credits,

     if(EXP_AMT<0,EE_Credits) as EECREDITS,

     if(EXP_AMT>0,EE_Credits) as EESPEND

FROM

(ooxml, embedded labels, table is [Separate the numbers]);

Drop Fields EE_Spend , EE_Credits;

[Anonymous]

‌

[Anonymous]

‌Thanks. I only have EXP_AMT field in the data. So I also need to create the SPEND and CREDITS fields in addition to doing the separation in QV, Preferably at the same time. Any further ideas?

Your test will work fine if I already had the additional fields, unfortunately I don’t.

My data set is too large to create fields externally and then bring in a new QVD.

[Anil_Babu_Samineni]

Is that question still pending? Or got the answer?

[Anonymous]

Not really answered yet, I only have one numerical field in the data (EXP_AMT), I want to create a further two,(EXP_SPEND) which has positive numbers AND also (EXP_CREDIT) field which has negative amounts. I would like to know if it possible to do this in a script. Thanks for getting back to me.

[wdchristensen]

So what is wrong with arvind654 solution? It looks to me like it does exactly what you asked for.

LOAD EXP_AMT,

     if(EXP_AMT<0,EE_Credits) as EE_Spend,

     if(EXP_AMT>0,EE_Credits) as EE_Credits

[Anonymous]

I Only have one numeric field.  EE_credits is not there

‌

[wdchristensen]

LOAD EXP_AMT,

     if(EXP_AMT<0,EXP_AMT) as EE_Spend,

     if(EXP_AMT>0,EXP_AMT) as EE_Credits

[wdchristensen]

Your attachment contained 3 before fields and 3 desired output fields which I believe was the source of the confusion.