topic Joining two tables in QlikView

Joining two tables

Anonymous — Thu, 10 Jul 2014 13:17:37 GMT

Hello, I am stuck with a "field not found" error when i tried joining two tables. I tried to join two different types of sales - one being an oem sales and the other is distributor's sales. I concatenated all the oem sales for 2 periods and called it as revenue table. Then I concatenated the other distributor sale table and stored it as disti_revenue.qvd. I then left join it to the revenue table, the scripts below:

left join (Revenue)

LOAD

%Calendar_Id as POS_Calendar_Id,

%Transaction_Id as POS_Transaction_Id,

%Material_Id,

%Product_Line_Id as POS_Product_Line_Id,

%End_Mkt_Id as POS_End_Mkt_Id,

%Distributor_Id as POS_Distributor_Id,

%Branch_Id as POS_Branch_Id,

%PV_Cust_Ship_Id as POS_PV_Cust_Ship_Id,

%PV_Cust_Sold_Id as POS_PV_Cust_Sold_Id,

%PV_Cust_Parent_Id as POS_PV_Cust_Parent_Id,

%PV_Territory_Id as POS_PV_Territory_Id,

%EC_Cust_Ship_Id as POS_EC_Cust_Ship_Id,

%EC_Cust_Sold_Id as POS_EC_Cust_Sold_Id,

%EC_Cust_Parent_Id as POS_EC_Cust_Parent_Id,

%EC_Territory_Id as POS_EC_Territory_Id,

Channel_Type as POS_Channel_Type,

POS_Qty,

POS_Revenue_Value,

POS_Resale_Value,

POS_Disti_Value,

POS_Direct_Cost,

FROM Disti_Revenue.Qvd;

The error message that i got:

Field not found - <%Calendar_Id>

left join (Revenue)

Please help!! Thanks in advance.

Re: Joining two tables

ali_hijazi — Thu, 10 Jul 2014 13:19:27 GMT

put names between brackets

[%Calendar_Id]

Re: Joining two tables

Anonymous — Thu, 10 Jul 2014 13:24:44 GMT

Thanks, i tried that but it still gives me the same error.

Error details:

ScriptError

ScriptErrorCount 1

ScriptErrorDetails <NULL>

ScriptErrorList Field Not Found

Re: Joining two tables

jonathandienst — Thu, 10 Jul 2014 13:29:34 GMT

As the error is complaining about the first field,

it could be due to the QVD file not being found,

or it could be due to the QVD not containing that field.

Remember that QV field names are case sensitive, so check that it is %Calendar_Id and not %Calendar_ID or %Calendar_id.

HTH

Jonathan

Re: Joining two tables

Thu, 10 Jul 2014 14:06:34 GMT

There is a comma before FROM Disti_Revenue.Qvd;

Re: Joining two tables

Anonymous — Thu, 10 Jul 2014 23:00:21 GMT

Hi guys... the error's gone and i now tried to merge the two files together but for whatever reason QV kept crashing on me... Can you guys help please??

This is the oem table:

Revenue:

LOAD %Calendar_Id,

%Transaction_Id,

%Material_Id,

%Product_Line_Id,

%End_Mkt_Id,

%Distributor_Id,

%Branch_Id,

%PV_Cust_Ship_Id,

%PV_Cust_Sold_Id,

%PV_Cust_Parent_Id,

%PV_Territory_Id,

%EC_Cust_Ship_Id,

%EC_Cust_Sold_Id,

%EC_Cust_Parent_Id,

%EC_Territory_Id,

Channel_Type,

Ship_Qty,

Ship_Value,

Ship_Direct_Cost,

Ship_Full_Cost,

Ship_Direct_Margin,

Ship_Full_Margin,

'Ship' as RevType

FROM

Revenue.qvd

(qvd);

Distributor table:

DistiRevenue:

LOAD %Calendar_Id as POS_Calendar_Id,

%Transaction_Id as POS_Transaction_Id,

%Material_Id,

%Product_Line_Id as POS_Product_Line_Id,

%End_Mkt_Id as POS_End_Mkt_Id,

%Distributor_Id as POS_Distributor_Id,

%Branch_Id as POS_Branch_Id,

%PV_Cust_Ship_Id as POS_PV_Cust_Ship_Id,

%PV_Cust_Sold_Id as POS_PV_Cust_Sold_Id,

%PV_Cust_Parent_Id as POS_PV_Cust_Parent_Id,

%PV_Territory_Id as POS_PV_Territory_Id,

%EC_Cust_Ship_Id as POS_EC_Cust_Ship_Id,

%EC_Cust_Sold_Id as POS_EC_Cust_Sold_Id,

%EC_Cust_Parent_Id as POS_EC_Cust_Parent_Id,

%EC_Territory_Id as POS_EC_Territory_Id,

Channel_Type as POS_Channel_Type,

POS_Qty,

POS_Revenue_Value,

POS_Resale_Value,

POS_Disti_Value,

POS_Direct_Cost,

POS_Full_Cost,

POS_Direct_Margin,

POS_Full_Margin,

POS_Disti_Margin

FROM

Distributor.qvd

(qvd);

merging the tables:

left join (Revenue)

LOAD

POS_Calendar_Id,

POS_Transaction_Id,

%Material_Id,

POS_Product_Line_Id,

POS_End_Mkt_Id,

POS_Distributor_Id,

POS_Branch_Id,

POS_PV_Cust_Ship_Id,

POS_PV_Cust_Sold_Id,

POS_PV_Cust_Parent_Id,

POS_PV_Territory_Id,

POS_EC_Cust_Ship_Id,

POS_EC_Cust_Sold_Id,

POS_EC_Cust_Parent_Id,

POS_EC_Territory_Id,

POS_Channel_Type,

POS_Qty,

POS_Revenue_Value,

POS_Resale_Value,

POS_Disti_Value,

POS_Direct_Cost,

POS_Full_Cost,

POS_Direct_Margin,

POS_Full_Margin,

POS_Disti_Margin

FROM

Disti_Revenue.Qvd

(qvd);

Re: Joining two tables

jagan — Fri, 11 Jul 2014 02:51:17 GMT

Hi,

I think you are trying to do UNION ALL for the two tables, if I am correct then try like this instead of left join (Revenue)

Revenue:

LOAD *

FROM

Revenue.qvd

(qvd);

Concatenate (Revenue)

LOAD *

FROM

Distributor.qvd

(qvd);

Concatenate (Revenue)

LOAD

FROM

Disti_Revenue.Qvd

(qvd);

Regards,

Jagan.

Re: Joining two tables

Anonymous — Fri, 11 Jul 2014 02:56:05 GMT

Hi, I have tried that way too and it works but the problem is I also need to link the combined revenue table to other fields, i have used applymap and it doesn't work. Someone mentioned that it's because it's not concatenating properly..

Thanks,

Re: Joining two tables

jagan — Fri, 11 Jul 2014 03:01:33 GMT

HI,

I am not sure what you are trying to do. Attach some sample data in excel file and your output, so that it would be easier to give solution.

Regards,

Jagan.

Re: Joining two tables

Anonymous — Fri, 11 Jul 2014 03:08:11 GMT

Thanks for your help! So I am trying to link the fiscal qtr and vintage year qtr per the scripts below to the revenue table. I tried joining them and it debugged fine except when i tested the vintage qtr in a list box, it's empty... so i guess it's not pulling correctly?

Please advise, thanks!

//*****************************************************************************Join Table Calendar

Left join (Revenue)

Load

// %Calendar_Id,

Fiscal_Qtr

FROM

Calendar.QVD

(qvd);

//*****************************************************************************Join Vintage Qtr Calendar

Left join (Revenue)

Load

%Material_Id,

Vintage_Year_Qtr

FROM

Material.QVD

(qvd);

Re: Joining two tables

Anonymous — Fri, 11 Jul 2014 03:10:39 GMT

Also, I stored the revenue table in a CombinedRevenue.QVD and i opened the QVD itself, there's nothign in the distributor's revenue..