topic Re: how would you join these Tables in QlikView

how would you join these Tables

Fri, 28 Feb 2014 07:49:18 GMT

i have three tables i don't want to use any joins here

Table1:

UserID

DealarCode

Date

Ammount

UserID_1

DealarCode 1

01-01-14

2214

Table2:

UserID

DealarCode

Date

Quantity

UserID_1

DealarCode 1

01-01-14

Table3:

UserID

DealarCode

Date

Tax

UserID_1

DealarCode 1

01-01-14

115

am using Concatenate

and getting this result

UserID	DealarCode	Date	Ammount	Quantity	Tax
UserID_1	DealarCode 1	01-01-14	2214
UserID_1	DealarCode 1	01-01-14		36
UserID_1	DealarCode 1	01-01-14			115

i want result like this

UserID	DealarCode	Date	Ammount	Quantity	Tax
UserID_1	DealarCode 1	01-01-14	2214	36	115

Re: how would you join these Tables

alexandros17 — Fri, 28 Feb 2014 07:53:05 GMT

MyTable:

load * resident Table1;

join

load * resident Table2;

join

load * resident Table3;

But you'll obtain the same result leaving the 3 tables as they are, let them associate (with a syntetic key)

Re: how would you join these Tables

AbhijitBansode — Fri, 28 Feb 2014 08:18:23 GMT

Use below script:

Load
      UserID,
      DealerCode,
      Date,
      Amount               AS Measure
      'Amount'             AS Type
From Table1;
Concatenate
Load
      UserID,
      DealerCode,
      Date,
      Quantity             AS Measure
      'Quantity'           AS Type
From Table2;
Concatenate
Load
      UserId,
      DealerCode,
      Date,
      Tax                   AS Measure
      'Tax'                 AS Type
From Table3;

Use Set analysis for expressions like:

Amount: sum ({<Type={'Amount'}>}Measure)

Re: how would you join these Tables

hic — Fri, 28 Feb 2014 08:58:03 GMT

You could do it in two steps, first concatenate, then aggregate:

TempTable:

Load * From Table1;

Concatenate Load * From Table2;

Concatenate Load * From Table3;

Final:

Noconcatenate

Load UserID, DealarCode, Date,

Sum(Ammount) as Amount,

Sum(Quantity) as Quantity,

Sum(Tax) as Tax

Resident TempTable

Group By UserID, DealarCode, Date;

Drop Table TempTable;

HIC

Re: how would you join these Tables

er_mohit — Fri, 28 Feb 2014 09:12:13 GMT

Try this code

Table1:

LOAD UserID & '|'&DealarCode&'|'&Date as Key2, * Inline [

UserID, DealarCode, Date, Ammount,

UserID_1, DealarCode 1, 01-01-14, 2214

];

Table2:

LOAD

UserID & '|'&DealarCode&'|'&Date as Key2,

* Inline [

UserID, DealarCode, Date, Quantity

UserID_1, DealarCode 1, 01-01-14, 36

];

Table3:

LOAD UserID & '|'&DealarCode&'|'&Date as Key2,* Inline [

UserID, DealarCode, Date, Tax

UserID_1, DealarCode 1, 01-01-14, 115

];

DROP Fields UserID,DealarCode,Date From Table1;

DROP Fields UserID,DealarCode,Date From Table2;

Re: how would you join these Tables

Fri, 28 Feb 2014 15:43:31 GMT

hi all i have done like this

Quantity:

Mapping LOAD

UserID & DealarCode & Date as Key,

Quantity

FROM

Quantity

Tax:

Mapping LOAD

UserID & DealarCode & Date as Key,

Tax

FROM

Tax

Ammount:

LOAD

UserID,

DealarCode,

UserID & DealarCode as MKey,

UserID & DealarCode & Date as Key,

Date,

Ammount

FROM

Ammount

All:

LOAD * ,

ApplyMap('Quantity',Key,'0') as 'Quantity',

ApplyMap('Tax',Key,'0') as 'Tax'

Resident Ammount;

DROP Table Ammount;