Facing issue while using cross table

[vlakeshr]

I have client data and data count is 1427. I have been used Cross table on server but i am getting wrong calculation across each field

Field containing "Yes" as remark in cell. Source format is excel.

I have client data and data count is 1427. I have been used Cross table on server but i am getting wrong calculation across each field

Field containing "Yes" as remark in cell. Source format is excel( Attched)

using script below script

CrossTable(Status, Value, 2)

LOAD Order_Number,

     MAN_Contry_Code,

    Credit_block,

     Incomplete_BOM,

     Legal_block,

     Order_placed_inside_DLT,

     Order_placed_without_matching_forecast,

     Other,

     TL_ULO_not_respected,

     ATP_fails,

     Wrong_missing_Incoterms,

     Wrong_missing_payment_terms,

     Wrong_missing_price,

     Wrong_missing_route,

     Wrong_missing_vendor_info_record

FROM

\\FRIAPFIL04.EAME.SYNGENTA.ORG\Poland$\SCM\ORFT_comment_tbl.xls

(biff, embedded labels, table is ORFT_comment_tbl$);

Field which are bold that we have consider as status. But each status giving same no. of count as per order no.

Each field containing status as 'Yes' and blank cell.

I am using below formula of set analysis but getting total count of all fields ( like each field total count * No. of status , 1427 * 13 =  18551 )

=Count({<Table={'ORFT_comment_tbl'}>}Order_Number)

Used this formula to calculate the count of status but not getting correct value.

Could you please suggest what is going wrong..

[sunny_talwar · Accepted solution]

May be your want to a resident load

Table:

CrossTable(Status, Value, 2)

LOAD Order_Number,

    MAN_Contry_Code,

    Credit_block,

    Incomplete_BOM,

    Legal_block,

    Order_placed_inside_DLT,

    Order_placed_without_matching_forecast,

    Other,

    TL_ULO_not_respected,

    ATP_fails,

    Wrong_missing_Incoterms,

    Wrong_missing_payment_terms,

    Wrong_missing_price,

    Wrong_missing_route,

    Wrong_missing_vendor_info_record

FROM

\\FRIAPFIL04.EAME.SYNGENTA.ORG\Poland$\SCM\ORFT_comment_tbl.xls

(biff, embedded labels, table is ORFT_comment_tbl$);

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where Len(Trim(Value)) > 0;

DROP Table Table;