Joining two tables

[Anonymous]

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.

[ali_hijazi]

put names between brackets

[%Calendar_Id]

[Anonymous]

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

Error details:

ScriptError   

ScriptErrorCount    1

ScriptErrorDetails    <NULL>

ScriptErrorList    Field Not Found

[jonathandienst]

Hi

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

[#5]

There is a comma before FROM Disti_Revenue.Qvd;

[Anonymous]

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);

[jagan]

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.

[Anonymous]

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,

W

[jagan]

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.

[Anonymous]

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);