How will I get values matching with the codes

[kavita25]

I have attached the excel file.

In Excel I want to make One column for Codes and One column for Values.

And it should show data based on the Values and Codes

for e.g

Tran_Ref no 15/09417

Cal Code WGT in  CALCODE1  the values is 0.6

Result: One column with a name "CODE"

If i click on WGT same Ref no. it should show values 0.6 from One column "Values"

Regards,

Kavita

[Mark_Little]

Something like this?

Data:

LOAD UNIT_CODE,

     TRN_CODE,

     TRN_REF_NO,

     TRN_LINE_NO,

     REF_NO,

     REF_LINE_NO,

     ITEM_CODE,

     ITEM_REV_NO,

     ITEM_DESC,

     STORE_CODE_FROM,

     STORE_CODE_TO,

     ITEM_UOM,

     ITEM_QTY,

     CONV_UOM,

     ITEM_CONV_QTY,

     ITEM_RATE,

     CAL_CODE1,

     VALUE1,

     CAL_CODE2,

     VALUE2,

     CAL_CODE3,

     VALUE3,

     CAL_CODE4,

     VALUE4,

     CAL_CODE5,

     VALUE5

FROM

[..\..\Downloads\TRN_DET - Copy.xls]

(biff, embedded labels, table is [Sheet 1$]);

NoConcatenate

Sorted:

Load

  1  as Type,

  UNIT_CODE,

     TRN_CODE,

     TRN_REF_NO,

     TRN_LINE_NO,

     REF_NO,

     REF_LINE_NO,

     ITEM_CODE,

     ITEM_REV_NO,

     ITEM_DESC,

     STORE_CODE_FROM,

     STORE_CODE_TO,

     ITEM_UOM,

     ITEM_QTY,

     CONV_UOM,

     ITEM_CONV_QTY,

     ITEM_RATE,

     CAL_CODE1 as CAL_Code,

     VALUE1    as Value

Resident Data;

Concatenate(Sorted)

Load

  2  as Type,

  UNIT_CODE,

     TRN_CODE,

     TRN_REF_NO,

     TRN_LINE_NO,

     REF_NO,

     REF_LINE_NO,

     ITEM_CODE,

     ITEM_REV_NO,

     ITEM_DESC,

     STORE_CODE_FROM,

     STORE_CODE_TO,

     ITEM_UOM,

     ITEM_QTY,

     CONV_UOM,

     ITEM_CONV_QTY,

     ITEM_RATE,

     CAL_CODE2 as CAL_Code,

     VALUE2    as Value

Resident Data;

//Do for other values and cal codes.

Drop Table Data;

You can then use the type field to chose which value or cal_code you want to use.

Mark

[Anonymous]

if you came to know any other solution, let me know

[settu_periasamy]

May be try to use the for loop with concatenate..

Like

For i = 1 to 40

If i=1 then

New:

Load 'CAL_CODE' $(i) as Flag,

YourFields

CALCODE1 as CAL_CODE,

VALUE1 as VALUE

Resident yourtable;

Else

Concatenate(New)

Load 'CAL_CODE' $(i) as Flag,

YourFields

CALCODE $(i) as CAL_CODE,

VALUE $(i) as VALUE

Resident yourtable;

Endif

Next i

Drop your resident table

Syntax may be incorrect. Sending via mobile..