Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mentrier
Contributor II
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

1 Solution

Accepted Solutions
PrashantSangle

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

6 Replies
amit_saini
Master III
Master III

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

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
Master III
Master III

All the logics above replied are fine.

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

PrashantSangle

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable

Hi,

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

mentrier
Contributor II
Contributor II
Author

Hi all

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

you made my day.