topic Re: How to separate + and - numbers from a numeric field? in QlikView

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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..

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

MK9885 — Wed, 29 Aug 2018 15:38:02 GMT

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;

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

Anonymous — Thu, 30 Aug 2018 07:32:21 GMT

‌

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

Anonymous — Thu, 30 Aug 2018 07:46:57 GMT

‌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.

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

Anil_Babu_Samineni — Thu, 30 Aug 2018 12:57:14 GMT

Is that question still pending? Or got the answer?

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

Anonymous — Fri, 31 Aug 2018 11:25:36 GMT

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.

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

wdchristensen — Fri, 31 Aug 2018 15:58:15 GMT

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

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

Anonymous — Fri, 31 Aug 2018 16:22:18 GMT

I Only have one numeric field. EE_credits is not there

‌

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

wdchristensen — Fri, 31 Aug 2018 18:19:32 GMT

LOAD EXP_AMT,

if(EXP_AMT<0,EXP_AMT) as EE_Spend,

if(EXP_AMT>0,EXP_AMT) as EE_Credits

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

wdchristensen — Fri, 31 Aug 2018 18:21:11 GMT

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

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

Anonymous — Sun, 02 Sep 2018 01:47:31 GMT

‌yes, the attachment was confusing but thought my text explained the problem. Anyway now resolved by adding a field, saving to another QVD and reloading.then running info from Shahbaz. I just thought there was an easier way.. thanks to all for your help.