Join next to specific value in a field

[ranibosch]

Good day, please refer to my scrip below:

//************************DEFAULT DIMENSION ON ITEMS***********************

DefaultDimension:

Load

    "No_" as "Item No",

    "Dimension Code" as "Trending Code",

  "Dimension Value Code" as "Trending Value";

SQL SELECT

"No_",

    "Dimension Code",

    "Dimension Value Code"

FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$Default Dimension"

where "Dimension Code" in ('CHANNEL', 'MFGTYPE', 'PACKSIZE', 'SERVICE');

******************************************************************************************************

I would like to join a field from another table called "Packsize UOM".

So basically it should do a vlookup on the trending value for packsize trending codes only, and then return the "Packsize UOM" next to each trending value

[sunny_talwar]

Would you be able to share a sample to show what exactly you are looking to do?

[ranibosch]

Please see screenshot below of field I would like to add:

[prma7799 · Accepted solution]

Try like this

DefaultDimension:

Load

    "No_" as "Item No",

    "Dimension Code" as "Trending Code",

  "Dimension Value Code" as "Trending Value";

SQL SELECT

"No_",

    "Dimension Code",

    "Dimension Value Code"

FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$Default Dimension"

Left join (DefaultDimension)

"Item No",

"Packsize UOM"

FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$Unit of Measure"

where "Trending Code" = 'PACKSIZE';

[ranibosch]

Thank you..