topic Re: create a field calculated from 2 different tables in QlikView

create a field calculated from 2 different tables

mentrier — Fri, 28 Nov 2014 02:17:55 GMT

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

Re: create a field calculated from 2 different tables

amit_saini — Fri, 28 Nov 2014 03:32:32 GMT

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

B*B1,

Resident Temp;

Thanks,

Re: create a field calculated from 2 different tables

Fri, 28 Nov 2014 04:40:56 GMT

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

Re: create a field calculated from 2 different tables

sujeetsingh — Fri, 28 Nov 2014 04:43:13 GMT

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

PrashantSangle — Fri, 28 Nov 2014 04:48:42 GMT

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,

Re: create a field calculated from 2 different tables

Fri, 28 Nov 2014 05:47:25 GMT

Hi,

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

Re: create a field calculated from 2 different tables

mentrier — Fri, 28 Nov 2014 17:00:09 GMT

Hi all

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

you made my day.