Set Analysis linking different QVD fields

[#1]

My data is as follows:

QVD 1:
Sales Order     Amount
4040  1000

QVD 2:
Job No  Item Code Type
4040  Paper  ABC
4040  LT-05  XYZ

Note: Although Job No = Sales Order No, it is not specified anywhere.

Now I want following Report output:

Sales Order      Amount      Color Code
4040                 1000          05(Take last 2 digits of Item Code where Type = XYZ and Job No = Sales Order)

How do I do this?

[#2]

Re-posting to avoid any confusion...

My data is as follows:

QVD 1:
Sales_Order     Amount
4040                1000

QVD 2:
Job_No  Item_Code      Type
4040       Paper            ABC
4040       LT-05            XYZ

Note: Although Job_No = Sales_Order, it is not specified anywhere.

Now I want following Report output:

Sales_Order      Amount      Color_Code
4040                 1000          05(Take last 2 digits of Item_Code where Type = XYZ and Job_No = Sales_Order)

How do I do this?

[rohit214]

hi  try this

use dimension =if(sales order=job no,sales order ,0) and in expression use Amount and subfield(colourcode,'-',2)

may it helps u

thanks

rohit