Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

mentrier
New Contributor II

create a field calculated from 2 different tables


Hi,

I have 2 tables loaded from excel & would like to calculate in the loading script an aggregated field that I could use as a dimension or expression when designing graphs.

file1:
LOAD [Sales Order] as Order,
    
requested_date
FROM

(
ooxml, embedded labels, table is Sheet1); 

  

file12
LOAD [Sales Order] as Order,
    
shipment_date
FROM

(
ooxml, embedded labels, table is Sheet1); 

& field to add:

networkdays (requested_date,shipment_date)-1 as on_time

anyone could help me?

thank you

Tags (1)
1 Solution

Accepted Solutions

Re: create a field calculated from 2 different tables

Hi,

Try with Outer Join which avoid creating extra line which is created by concatenate

try below script

file1:
LOAD [Sales Order] as Order,
    
requested_date
FROM

(
ooxml, embedded labels, table is Sheet1); 

Join
LOAD [Sales Order] as Order,
    
shipment_date
FROM

(
ooxml, embedded labels, table is Sheet1); 


Finalfile:

Load Order,

requested_date,

shipment_date,

networkdays(requested_date,shipment_date)-1 as on_time

resident file1;

Regards,

Regards,
Prashant Sangle
6 Replies
amit_saini
Honored Contributor III

Re: create a field calculated from 2 different tables

Michel,

It should be like this:

Example:

Test1:

Load

A, B

from temp1;

Test2:

Load A,B1,C

from temp2:

The temporary table will be:

Temp :

Load

A, B

from temp1;

Concatenate (Temp)

Load A,B1,C

from temp2:

Finally for calculation:

Load

A,

B*B1,

C

Resident Temp;

Thanks,

AS

Not applicable

Re: create a field calculated from 2 different tables

Hi Michel,


I think this script may helpful to you

file1:
LOAD [Sales Order] as Order,
    
requested_date
FROM

(
ooxml, embedded labels, table is Sheet1); 

concatenate  
LOAD [Sales Order] as Order,
    
shipment_date
FROM

(
ooxml, embedded labels, table is Sheet1); 



Finalfile:

Load Order, requested_date,shipment_date, networkdays(requested_date,shipment_date)-1 as on_time resident file1;

Thanks

Harsha

sujeetsingh
Honored Contributor III

Re: create a field calculated from 2 different tables

All the logics above replied are fine.

You need to join or concatenate the tables and then derieve the field.

Re: create a field calculated from 2 different tables

Hi,

Try with Outer Join which avoid creating extra line which is created by concatenate

try below script

file1:
LOAD [Sales Order] as Order,
    
requested_date
FROM

(
ooxml, embedded labels, table is Sheet1); 

Join
LOAD [Sales Order] as Order,
    
shipment_date
FROM

(
ooxml, embedded labels, table is Sheet1); 


Finalfile:

Load Order,

requested_date,

shipment_date,

networkdays(requested_date,shipment_date)-1 as on_time

resident file1;

Regards,

Regards,
Prashant Sangle
Not applicable

Re: create a field calculated from 2 different tables

Hi,

Sry please remove the 'concatenate' in the above script. By default it will take the Outer Join.

mentrier
New Contributor II

Re: create a field calculated from 2 different tables

Hi all

this works perfectly. 1st time getting in this forum. as friendly & performing than qlikview itself.

you made my day.

Community Browser