Preceding Load from 2 different Tables or how to calculate?

[#1]

Hi there,

i am new to qlikiew and i am using Preceding Load the first time.

I have 2 Excel Files:

File1:

     A

     B

     C          (i.e.   C = D - F)

     D

File2:

     E

     F

     G

In the Load Script i am loading all data from File1, but i also need the column F from file2 to calculate values for column C in file1.

Is there a possibility to solve the problem?

Thanks and best regards,

Sabrina

[alexandros17]

Hi Sabrina,

the question is: how the 2 files are linked? Does it exists a common field?

Send me the script you use ...

Let me know

[#3]

I need the field "Umsatz" from table Qlik_3 to calculate the field "Umsatz in €" in table Qlik_2:

LOAD Plattform,

     PlattformVJ,

     Datum,

     [Artikel Online],

     [Umsatz in € gesamt]  - Umsatz as [Umsatz in €],

     [Verkaufte Artikel],

     Deals_Liste,

     [Umsatz in € gesamt],

     DDMM

FROM

(ooxml, embedded labels, table is Qlik_2.de);

LOAD Start_Datum as Datum,

     Status,

     Art,

     Verkauft,

     Verfügbar,

     Preis,

     Besucher,

     Umsatz,

     Hersteller,

     Artikelnummer,

     Titel

FROM

(ooxml, embedded labels, table is Qlik_3);

Thanks a lot!!!

[Peter_Cammaert]

You won't necessarily need a Preceding LOAD for this.

A Preceding LOAD performs different subsequent processing steps on the same data stream before creating the final internal table. It's shorthand for writing different LOAD ... RESIDENT statements that follow a LOAD ... FROM for example, without the overhead of having to drop all the intermediate tables.

As Alessandro says, in your case you'll need to know what connects the records from the two files. If there isn't such a relationship, it will be almost impossible to connect the two together.

[#5]

The link between the tables is the field "Datum" (Date).

Thanks and regards!

[alexandros17]

I do not know the logic of your data but according to me try to understand which fields link a table with the other, I see only Datum but it is not enough, you should have to use artikel ...

[#7]

And thats the Problem!

In the field Artikel there is only the Artikel description and no ID ...

This is a indepentent file with sales volume, which i need to calculate the adjusted sales 😞

Theres no other possibility to link the tables 😞

Thx and regards,

Sabrina

[alexandros17]

Can you try to match Article description?

[#9]

Thats not possible. I have no Artikel Number in table1

Plattform	PlattformVJ	Datum	Artikel Online	Umsatz in €	Verkaufte Artikel	Deals_Liste	Umsatz in € gesamt	DDMM
A	A	01.01.2012	0	123,00 €	129	0,00 €	123,00 €	01.01.
A	A	02.01.2012	0	456,00 €	201	0,00 €	456,00 €	02.01.
B	B	03.01.2012	0	789,00 €	176	0,00 €	789,00 €	03.01.

Start_Datum	Status	Art	Verkauft	Verfügbar	Preis	Besucher	Umsatz	Hersteller	Artikelnummer	Titel
01.01.2012	Inaktiv	Value Hero	1412	6601	9,99	73.560	14.106	Name1	998877456	Artikel xyz
02.01.2012	Inaktiv	Daily Deal	80	1279	99,00	22.066	7.920	Name2	556644875	Artikel abc
03.01.2012	Inaktiv	Daily Deal	72	270	999,00	31.598	71.928	Name3	222255555	Artikel mno

[robert99]

This is a tricky issue as to the best way to do this.

One way to do this would be just for QV to join the tables (as long as date is in the same format) and then do the calculation in an expression in a chart.  Or use a variable in the expression in the chart

http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/12/to-join-or-not-to-join

2nd way would be to actual join the table (have one table rather than two) and then do a resident load to calculate  [Umsatz in €],.

Third way would be to do a lookup. By loading Qlik3 first and looking up Umsatz in the Qlik2 load.,

4th would be using apply map. Also by loading Qlik3 first either just the two field required or all fields and then do a resident load to get the two field needed for ther mapping load

http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

The apply map could be used to calculate to the formula direct into the Qlik2 load. So you would load Qlik3 first

I would either the first or mapping load option but it depends on circumstances