Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Something like this to use applymap
Qlik3:
LOAD Start_Datum as Datum,
Status,
Art,
Verkauft,
Verfügbar,
Preis,
Besucher,
Umsatz,
Hersteller,
Artikelnummer,
Titel
FROM
(ooxml, embedded labels, table is Qlik_3);
Qlik3Map:
mapping load
Datum,
Umsatz
resident Qlik3;
LOAD Plattform,
PlattformVJ,
Datum,
[Artikel Online],
[Umsatz in € gesamt] - applymap ('Qlik3Map',Datum, 'NoLU') as [Umsatz in €],
[Verkaufte Artikel],
Deals_Liste,
[Umsatz in € gesamt],
DDMM
FROM
(ooxml, embedded labels, table is Qlik_2.de);
instead of 'NoLU' you could use null() or say 1 or 0 etc. This is the default if a lookup is not found
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
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!!!
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.
The link between the tables is the field "Datum" (Date).
Thanks and regards!
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 ...
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
Can you try to match Article description?
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 |
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