topic Re: Calculate two collums diff. table with condition in script in QlikView

Calculate two collums diff. table with condition in script

Anonymous — Fri, 31 Aug 2018 00:52:27 GMT

Hello,

I have two tables: company and customer.

___________________________________________________________________________________

company:

LOAD DATE,

MACHINE,

plan_hrs,

[Mec. Hr.]

FROM (ooxml, embedded labels, table is company);

customer:

LOAD DATE,

MACHINE,

plan_hrs_cust,

[Mec. Hr._cust]

FROM (ooxml, embedded labels, table is customer);

___________________________________________________________________________________

I need to calculate in script the expression bellow:

if(sum(plan_hrs_cust)>0,

sum(plan_hrs_cust),

sum(plan_hrs))

If the hrs of the customer is greater than 0, then consider the customer's hours, else consider company's hrs.

I have in chart, but I want to do it in the script.

Follow the files.

Tks

Re: Calculate two collums diff. table with condition in script

Lisa_P — Fri, 31 Aug 2018 01:19:19 GMT

I note that you have a synthetic key here, it would be best to join these tables together and use the formula to create a new field.

Here is the script:

company:

LOAD DATE,

MACHINE,

plan_hrs,

[Mec. Hr.]

FROM ...

//customer:

Left Join(company)

LOAD DATE,

MACHINE,

plan_hrs_cust,

[Mec. Hr._cust]

FROM ...

New:

Load *,

If(plan_hrs_cust>0, plan_hrs_cust, plan_hrs) as plan_hrs_real

Resident company;

Drop table company;

Re: Calculate two collums diff. table with condition in script

Anonymous — Fri, 31 Aug 2018 02:36:27 GMT

Hi Samuel,

kindly concatenate two tables and create new field

company:

LOAD DATE,

MACHINE,

plan_hrs,

[Mec. Hr.]

FROM (ooxml, embedded labels, table is company);

Concatenate

customer:

LOAD DATE,

MACHINE,

plan_hrs_cust,

[Mec. Hr._cust]

FROM (ooxml, embedded labels, table is customer);

New:

LOAD

DATE,

MACHINE,

sum(plan_hrs)as plan_hrs,

sum(plan_hrs_cust)as plan_hrs_cust,

IF(SUM(plan_hrs_cust)>0,SUM(plan_hrs_cust),SUM(plan_hrs))as plan_hrs_real

Resident company Group by MACHINE,DATE;

drop table company;

Thanks

Re: Calculate two collums diff. table with condition in script

BalaBhaskar_Qlik — Sat, 01 Sep 2018 10:56:27 GMT

Company:

LOAD

DATE,

MACHINE,

plan_hrs,

[Mec. Hr.]

FROM (ooxml, embedded labels, table is company);

Concatenate

Customer:

LOAD

DATE,

MACHINE,

plan_hrs_cust,

[Mec. Hr._cust]

FROM (ooxml, embedded labels, table is customer);

NoConcatenate

Cust_Company:

LOAD

DATE,

MACHINE,

Sum(plan_hrs)as plan_hrs,

Sum(plan_hrs_cust)as plan_hrs_cust,

If(Sum(plan_hrs_cust) > 0, Sum(plan_hrs_cust), Sum(plan_hrs)) as plan_hrs_real

Resident Company

Group by DATE, MACHINE;

Drop Table Company;