topic Re: Help Needed For joining two tables in QlikView

Help Needed For joining two tables

Mon, 02 Jun 2014 15:23:18 GMT

Dear folks,

i have two tables. in the first table i have 12 columns in the second table i have 6 columns. in the both the table 3 commons GPO Material Material Group are common fields

when i use concateante (Tab1) to join the two tables there is no error showing . but the data is not loaded for second table please guide me where i am doing the mistake.shall i use any condition for that.

Re: Help Needed For joining two tables

MayilVahanan — Mon, 02 Jun 2014 15:34:24 GMT

Can you post the script?

Re: Help Needed For joining two tables

Mon, 02 Jun 2014 15:41:25 GMT

DATA:

LOAD GPO,

[GPO number],

Customer,

[Customer Num],

Tier,

[Material Group],

Material,

Volume,

[Projected Volume],

[WAC Sales],

[Contract Sales],

[Admin Fee],

[Standard Rebates],

[Growth Rebates],

[Net Sales],

Cost,

[Sales Incentive],

[GM $],

Month,

Quarter,

Year

FROM

[F1.xlsx]

(ooxml, embedded labels, table is Data);

//Inner Join IntervalMatch (GPO,Material,[Material Group])

join LOAD GPO,

Contract,

Material,

Name,

[Material Group],

[Contract Price],

[Tier 1],

[Tier 2],

[Tier 3]

FROM

[F1.xlsx]

(ooxml, embedded labels, table is [TIER INFO]);

Re: Help Needed For joining two tables

MayilVahanan — Tue, 03 Jun 2014 05:06:58 GMT

Form the key by using with three common field and joining with that field.

Re: Help Needed For joining two tables

jagan — Tue, 03 Jun 2014 05:39:57 GMT

Hi,

Try like this

DATA:

LOAD GPO,

[GPO number],

Customer,

[Customer Num],

Tier,

[Material Group],

Material,

Volume,

[Projected Volume],

[WAC Sales],

[Contract Sales],

[Admin Fee],

[Standard Rebates],

[Growth Rebates],

[Net Sales],

Cost,

[Sales Incentive],

[GM $],

Month,

Quarter,

Year

FROM

[F1.xlsx]

(ooxml, embedded labels, table is Data);

//Inner Join IntervalMatch (GPO,Material,[Material Group])

LEFT join LOAD GPO,

Contract,

Material,

Name,

[Material Group],

[Contract Price],

[Tier 1],

[Tier 2],

[Tier 3]

FROM

[F1.xlsx]

(ooxml, embedded labels, table is [TIER INFO]);

Qlikview automatically joins the tables based on the common columns, manually check whether there are any matching records in both the tables. Note: Qlikview is Case sensitive so the data in the fields should be in the same case.

Regards,

Jagan.

Regards,

Jagan.

Re: Help Needed For joining two tables

its_anandrjs — Tue, 03 Jun 2014 05:55:42 GMT

Try to load with the composite keys or otherwise use simple join between them

DATA:

LOAD

GPO&'_'&Material&'_'&[Material Group] as Key,

//GPO,

[GPO number],

Customer,

[Customer Num],

Tier,

//[Material Group],

//Material,

Volume,

[Projected Volume],

[WAC Sales],

[Contract Sales],

[Admin Fee],

[Standard Rebates],

[Growth Rebates],

[Net Sales],

Cost,

[Sales Incentive],

[GM $],

Month,

Quarter,

Year

FROM

[F1.xlsx]

(ooxml, embedded labels, table is Data);

join

LOAD

GPO&'_'&Material&'_'&[Material Group] as Key

//GPO, //rename this fields

Contract,

//Material, //rename this fields

Name,

//[Material Group], //rename this fields

[Contract Price],

[Tier 1],

[Tier 2],

[Tier 3]

FROM

[F1.xlsx]

(ooxml, embedded labels, table is [TIER INFO]);