topic Solved in QlikView

Solved

microwin88x — Wed, 11 Nov 2015 05:17:26 GMT

Solved

Re: Join Between Tables & Calculate Field

tamilarasu — Wed, 11 Nov 2015 05:50:04 GMT

Hi Microwin.

Check the attachment and let me know,

Table1:
LOAD Unit,
     Multiplier
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);

Left join

Table2:
LOAD Month,
     Amount
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
Final:
Load *,
Multiplier* Amount as Operation
Resident Table1 where len(Unit);

Drop table Table1;

Re: Join Between Tables & Calculate Field

gautik92 — Wed, 11 Nov 2015 05:52:26 GMT

Load

...

Multiplier*Amount as New ,

resident Table1;

Re: Join Between Tables & Calculate Field

jonathandienst — Wed, 11 Nov 2015 06:03:30 GMT

The script from TamilArasuis correct, except that the join should be a default (full outer) join, because you have requested a cartesian product. Remove the 'left' keyword.

Re: Join Between Tables & Calculate Field

Kushal_Chawda — Wed, 11 Nov 2015 06:04:35 GMT

you need to perform Cross join between two tables to generate the each possible combination

Data:

LOAD * Inline [

Month, Amount

Jan, 101

Feb, 78

Mar, 68

Apr, 42

May, 43

Jun, 68

Jul, 118

Aug, 150

Sep, 132

Oct, 112

Nov, 122

Dec, 26 ];

join

LOAD * Inline [

Unit, Multiplier

415, 17

418, 25

423, 11 ];

Final:

noconcatenate

LOAD *,

Multiplier*Amount as Operation

Resident Data;

DROP Table Data;

Re: Join Between Tables & Calculate Field

tamilarasu — Wed, 11 Nov 2015 06:10:16 GMT

Thanks Jonathan