topic How to perform division operation in script side in QlikView

How to perform division operation in script side

chinnuchinni — Thu, 04 Jan 2018 05:30:34 GMT

Hi Everybody,

my script is like below

sale:

LOAD Name,

Prd.Cat.No,

Sales,

'bill' as Category

FROM

(ooxml, embedded labels, table is Sheet1) where Prd.Cat.No =101;

Concatenate

LOAD Name,

Prd.Cat.No,

Sales,

'book' as Category

FROM

(ooxml, embedded labels, table is Sheet1) where Prd.Cat.No =102;

so i want to create one another table which is category type as "book / bill"

which is like

LOAD Name,

Prd.Cat.No,

Sales,

'book / bill' as Category

FROM

(ooxml, embedded labels, table is Sheet1) ;

How it is possible in script ?????????

Thanks in Advance.

Re: How to perform division operation in script side

Anil_Babu_Samineni — Thu, 04 Jan 2018 05:36:01 GMT

Are you facing any issue while loading this?

Re: How to perform division operation in script side

chinnuchinni — Thu, 04 Jan 2018 05:42:55 GMT

no,but i want to find the value " book / bill " sales in script side.

Re: How to perform division operation in script side

Anil_Babu_Samineni — Thu, 04 Jan 2018 05:44:50 GMT

What are you getting When you use this as filter "Category". I meant to say please paste this field in list box?

Re: How to perform division operation in script side

chinnuchinni — Thu, 04 Jan 2018 05:49:27 GMT

Here category is a flag.when i select the " bill " from category it will give bill info,when i select the " book " from category it will give book info, as well as i want to find " book / bill " value when i select the book/bill from category.

Hope you understand

Re: How to perform division operation in script side

chinnuchinni — Thu, 04 Jan 2018 05:51:25 GMT

for example bill = 200,book =400,

i want to calculate book/bill =2.

Re: How to perform division operation in script side

Anil_Babu_Samineni — Thu, 04 Jan 2018 05:57:01 GMT

Try to reply who you want to help. Not entirely sure, What you want to achieve here. can you make it simple using Inline tables

sale:

LOAD Name, Prd.Cat.No, Sales, 'bill' as Category

FROM

(ooxml, embedded labels, table is Sheet1) where Prd.Cat.No =101;

Concatenate

LOAD Name, Prd.Cat.No, Sales, 'book' as Category

FROM

(ooxml, embedded labels, table is Sheet1) where Prd.Cat.No =102;

LOAD Name, Prd.Cat.No, Sum(Sales) as Sales, 'book / bill' as Category

FROM

(ooxml, embedded labels, table is Sheet1) Where Match(Prd.Cat.No, 101, 102) Group By Name, Prd.Cat.No;

Re: How to perform division operation in script side

chinnuchinni — Thu, 04 Jan 2018 06:23:39 GMT

Sample:

LOAD * INLINE [

Name, Prd.No, Sales

abc, 101, 120

xyz, 101, 180

pqr, 101, 270

abc, 102, 30

xyz, 102, 60

pqr, 102, 90

];

load Name,

Sales,

Prd.No,

'Bill' as Category

Resident Sample Where Prd.No = 101;

load Name,

Sales,

Prd.No,

'Book' as Category

Resident Sample Where Prd.No = 102;

****

now i want to calculate another table which is "bill / book" ca category type

need to get " bill/book " =3

Re: How to perform division operation in script side

Anil_Babu_Samineni — Thu, 04 Jan 2018 12:38:50 GMT

Front end / Back end?

Re: How to perform division operation in script side

chinnuchinni — Thu, 04 Jan 2018 12:42:19 GMT

Back End

Re: How to perform division operation in script side

Anil_Babu_Samineni — Thu, 04 Jan 2018 13:02:36 GMT

Can you try this?

Sample:

LOAD * INLINE [

Name, Prd.No, Sales

abc, 101, 120

xyz, 101, 180

pqr, 101, 270

abc, 102, 30

xyz, 102, 60

pqr, 102, 90

];

Final:

NoConcatenate

load Prd.No, Sum(Sales) as Sales, 'Bill' as Category

Resident Sample Where Prd.No = 101 Group By Prd.No;

load Prd.No, Sum(Sales) as Sales, 'Book' as Category

Resident Sample Where Prd.No = 102 Group By Prd.No;

DROP Table Sample;

Last:

NoConcatenate

LOAD Sales, Prd.No, Category Resident Final;

Concatenate

LOAD Prd.No, 'Bill/Book' as Category, If(Prd.No = 101,Sum(Sales))/If(Prd.No = 102,Sum(Sales)) as Sales Resident Final Where Prd.No = 101 and Prd.No = 102 Group By Prd.No;

DROP Table Final;

Re: How to perform division operation in script side

PrashantSangle — Thu, 04 Jan 2018 13:06:59 GMT

You can create flag using if condition also. Try below

Sample:

LOAD Name,Prd.No,Sales,if(Prd.No=101,'Bill','Book') as Flag,if(Prd.No=101,Sales) as BillSales,if(Prd.No=102,Sales) as BookSales INLINE [

Name, Prd.No, Sales

abc, 101, 120

xyz, 101, 180

pqr, 101, 270

abc, 102, 30

xyz, 102, 60

pqr, 102, 90

];

concatenate

Load 'Bill/Book' as Flag,Sum(BillSales)/Sum(BookSales) as sales_div Resident Sample;

Regards,

Re: How to perform division operation in script side

Anil_Babu_Samineni — Thu, 04 Jan 2018 13:12:24 GMT

Are you sure, this will return 'Bill/Book' ?? I wonder, This may returns null()

Re: How to perform division operation in script side

PrashantSangle — Thu, 04 Jan 2018 13:24:42 GMT

Yes..

Re: How to perform division operation in script side

chinnuchinni — Fri, 05 Jan 2018 04:44:43 GMT

for bill/book it's giving value as Zero

Re: How to perform division operation in script side

PrashantSangle — Fri, 05 Jan 2018 05:13:39 GMT

PFA.

Regards,

Re: How to perform division operation in script side

chinnuchinni — Fri, 05 Jan 2018 05:45:31 GMT

Re: How to perform division operation in script side

PrashantSangle — Fri, 05 Jan 2018 06:06:33 GMT

In your earlier reply you are taking category as dimension. So logic is as per category field.

Replace Name field with Flag You will get desired result.

See below snap shot for your reference.

If it is not solving your requirement , mention what is your actual and complete requirement.

Regards,