topic Need Help in QlikView

Need Help

paulwalker — Wed, 15 Jul 2015 09:24:23 GMT

Hi All,

I have 2 tables almost 6 fields are same, remaining fields are different..

Like..

Table1:

Load

SalesID

ProdID,

CountryCode,

Sales

Date

ProdSesc

SalesAmt

From Sales;

Table2:

Load ProdID,

CountryCode,

Sales,

Date,

ProdDesc,

OrderAmt,

OredrName

From Orders;

i want to create link table, here should i create all common fields concatenate into one %Key field..

(Like ProdID & CountryCode & Sales & Date & ProdDesc as %Key)

any other method???

Re: Need Help

robert_mika — Wed, 15 Jul 2015 09:32:44 GMT

You can use As statement

Sales as T2Sales

or Qualify

Look here

Do you Qualify?- How to use QUALIFY statement

Feeling Qlikngry?

How To /Missing Manual(18 articles)

Re: Need Help

alexandros17 — Wed, 15 Jul 2015 09:32:54 GMT

Take a look to autonumberhash256() instruction and to the following document:

Re: Need Help

Gysbert_Wassenaar — Wed, 15 Jul 2015 09:33:41 GMT

Yes, that's the idea:

Table1:

LOAD

autonumber(ProdID & '|' & CountryCode &'|' & Sales &'|' & Date &'|' & ProdDesc) as %Key,

...other fields...

FROM ...Table1Source...;

Table2:

LOAD

autonumber(ProdID & '|' & CountryCode &'|' & Sales &'|' & Date &'|' & ProdDesc) as %Key,

...other fields...

FROM ...Table2Source...;

LinkTable:

LOAD distinct

autonumber(ProdID & '|' & CountryCode &'|' & Sales &'|' & Date &'|' & ProdDesc) as %Key,

ProdId,

CountryCode,

Sales,

Date,

ProdDesc

FROM ...Table1Source...;

Concatenate(LinkTable)

LOAD distinct

autonumber(ProdID & '|' & CountryCode &'|' & Sales &'|' & Date &'|' & ProdDesc) as %Key

ProdId,

CountryCode,

Sales,

Date,

ProdDesc

FROM ...Table2Source...;

Note that the common fields now only exist in the LinkTable. Table1 and Table2 only contain %Key and the fields that are unique to the tables.

Re: Need Help

Joaquin_Lazaro — Wed, 15 Jul 2015 09:38:11 GMT

Hi Paul:

You can join both tables in only one

Table1:

Load

SalesID

ProdID,

CountryCode,

Sales

Date

ProdSesc

SalesAmt

From Sales;

Left Join

Load ProdID,

CountryCode,

Sales,

Date,

ProdDesc,

OrderAmt,

OredrName

Hope this helps you

Joaquín

Re: Need Help

qlikmsg4u — Wed, 15 Jul 2015 09:43:42 GMT

Hi Paul,

Find the attached app