topic Re: Scripting in QlikView

Scripting

Mon, 22 Aug 2016 10:22:29 GMT

Hi Team,

Can any one give the solution.

These are actual table fields.

A B C

Health 3 -

Health - 15

but I require B/C in scripting.

Thanks

krish

Re: Scripting

sunny_talwar — Mon, 22 Aug 2016 10:25:06 GMT

You require B/C in scripting? You want to be able to divide them?

May be like this:

LOAD A,

Sum(B)/Sum(C) as [B/C]

From ...

Group By A;

Re: Scripting

avinashelite — Mon, 22 Aug 2016 10:25:10 GMT

something like this

LOAD A,

if(len(trim(B))>0,B,C) as B_C

from table ;

could you please explain what is the output your excepting ??

Re: Scripting

Mark_Little — Mon, 22 Aug 2016 10:27:43 GMT

Hi,

Just confirm should the final data look like

Health 3 15

Probably something like

DataTemp:

Load

From ...

Where LEN(B) > 0;

Join

Load

From ...

Where LEN(C) > 0;

NoConcatenate

Data:

LOAD

B / C AS D

RESIDENT DataTemp;

Drop table DataTemp;

Mark

Re: Scripting

Mon, 22 Aug 2016 10:35:08 GMT

Hi Sunny,

Did not get that scripting. It is returns the invalid expression.

I require Division of B/C.

Thanks

krish

Re: Scripting

sunny_talwar — Mon, 22 Aug 2016 10:37:39 GMT

Did you use Group By Statement?

Re: Scripting

Mon, 22 Aug 2016 10:39:46 GMT

Hi Avinash,

I require only Division of B/C.

Thanks

Giri

Re: Scripting

Mon, 22 Aug 2016 10:40:31 GMT

Yes

Re: Scripting

Anonymous — Mon, 22 Aug 2016 10:46:56 GMT

Hi,

Try this code:

MyTable:
LOAD * INLINE [
    A, B, C
    Health,3,
    Health,,15
];

Final:
LOAD
    A,
    Sum(If(IsNull(B),0,B)) as SumB,
    Sum(If(IsNull(C),0,C)) as SumC,
    IF(Sum(If(IsNull(C),0,C)) = 0,0,
        Sum(If(IsNull(B),0,B))/Sum(If(IsNull(C),0,C))) as [B/C]
RESIDENT MyTable
Group By A;

Use IsNull to set 0 when B or C is null, if Sum(C) is null it will return 0 on B/C field.

Regards!

Re: Scripting

Mon, 22 Aug 2016 10:50:22 GMT

Hi Sunny,

I require B,C fields also. It is possible to without B,C fields.

Re: Scripting

sunny_talwar — Mon, 22 Aug 2016 10:53:56 GMT

May be this:

LOAD A,

RangeMax(B) as B,

RangeMax(C) as C,

Sum(B)/Sum(C) as [B/C]

From ...

Group By A;

Re: Scripting

florentina_doga — Mon, 22 Aug 2016 10:57:43 GMT

try this

load * inline [A, B, C

Health, 3,

Health,, 15];

zz:

load *,(b/c) as bc;

load A,

sum(alt(B,0)) as b,

sum(alt(C,0)) as c

resident z group by A;

drop table z;

Re: Scripting

sunny_talwar — Mon, 22 Aug 2016 10:58:01 GMT

My bad, may be this:

LOAD A,

Sum(B) as B,

Sum(C) as C,

Sum(B)/Sum(C) as [B/C]

From ...

Group By A;

Re: Scripting

Anil_Babu_Samineni — Mon, 22 Aug 2016 11:08:16 GMT

A B C

Health 3 -

Health - 15

From this What are you expecting

A B C B/C

Health 3 - 3/-

Health - 15 -/15

Re: Scripting

sunny_talwar — Mon, 22 Aug 2016 11:12:58 GMT

I think something like this:

Re: Scripting

Thu, 25 Aug 2016 05:06:26 GMT

Hi Sunny,

Thank you for given information.

Regards,

krish