Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculate two collums diff. table with condition in script

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

1 Solution

Accepted Solutions
Lisa_P
Employee
Employee

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;

View solution in original post

3 Replies
Lisa_P
Employee
Employee

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;

Anonymous
Not applicable
Author

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

balabhaskarqlik

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;