apply map

[ravindraa]

i have stuck one place if any one guide me suggestion how to do the below thing.

we have two data base's both database have status field and but one database have ACTIVE, CANCELLED,CREATED,NA, another one have ACTIVE , CANCELLED,ENROLLED we need to display the values based on the status wise that's why i concatenate both DB status fields AS COMMON STATUS field.

as well as we have joined both DB tables as inner join, here i want to store COMMON STATUS in the inner join table like below i wrote script , but i am getting script failed.

finaly i want add common status into the below inner join table.

Talismastatusconcate:

Mapping

LOAD

                [Customer ID]&[Contract ID]&Business as TS_TCB2,

                 Upper(Status)as Commonstatus

FROM

(qvd);

Concatenate                                                                                             **************concatinate script

sapstatusconcatinate:

load

                    num(Customer)&num([Contract No])&[Contract Type] as TS_TCB2,

                    [Contract Status] as Commonstatus

FROM

COMMONONTALISMA:

LOAD

     [Contract ID] as TS_O_Contract,

     [Customer ID]&[Contract ID]&Business as TS_TCB2,

    Upper(Status)as TS_O_STATUS,

    //[Customer ID]&[Contract ID]&Business as TS_TCB1,

     [Contract ID]&Upper(Status) as TS_ONLYTALISMA

FROM

(qvd);

Inner Join(COMMONONTALISMA)                                                                      ************inner join

LOAD

     num([Contract No]) as S_O_Contract,

    [Contract Status] as S_o_Status,

     num(Customer)&num([Contract No])&[Contract Type] as TS_TCB2,

      ApplyMap('Talismastatusconcate',TS_TCB2)as Commonstatus1,                      ******apply map script

     num([Contract No])&[Contract Status] as S_ONLYSAP

FROM

(qvd)Where NOT Exists('TS_ONLYTALISMA',num([Contract No])&[Contract Status]);

please give me suggestion.................

[nam]

firstly I don't think you can concatenate to that mapping table, do the concatenation first then create the mapping table from the concatenated table

[its_anandrjs]

Try this way

//Concatenate this tables first

TmpTalismastatusconcate:

LOAD

        [Customer ID]&[Contract ID]&Business as TS_TCB2,

        Upper(Status)as Commonstatus

FROM

(qvd);

Concatenate                            //**************concatinate script

sapstatusconcatinate:

load

                    num(Customer)&num([Contract No])&[Contract Type] as TS_TCB2,

                    [Contract Status] as Commonstatus

FROM

;

//===========================Load this mapping table and then

Talismastatusconcate:

Mapping Load

TS_TCB2,

Commonstatus

Resident TmpTalismastatusconcate;

DROP Table Talismastatusconcate;

//===========================

COMMONONTALISMA:

LOAD

     [Contract ID] as TS_O_Contract,

     [Customer ID]&[Contract ID]&Business as TS_TCB2,

     Upper(Status)as TS_O_STATUS,

    //[Customer ID]&[Contract ID]&Business as TS_TCB1,

      [Contract ID]&Upper(Status) as TS_ONLYTALISMA

FROM

(qvd);

Inner Join(COMMONONTALISMA)                                                       //************inner join

LOAD

     num([Contract No]) as S_O_Contract,

    [Contract Status] as S_o_Status,

     num(Customer)&num([Contract No])&[Contract Type] as TS_TCB2,

     ApplyMap('Talismastatusconcate',TS_TCB2)as Commonstatus1,      //******apply map script

     num([Contract No])&[Contract Status] as S_ONLYSAP

FROM

(qvd)Where NOT Exists('TS_ONLYTALISMA',num([Contract No])&[Contract Status]);

Let me know if problem persists.

Note:- In the second table named COMMONONTALISMA also if you join this tables and then use apply map table for the mapping.

[ravindraa]

Hi anand chouhan,

thanks for the suggesion i did as far your inputs but i am not getting  one value in the commonstatus1 (this field should be display ACTIVE, CANCELLED, ENROLLED, NA, CREATED) but i am not getting  NA value i wrote the logic like below. Please find the

TMPTalismastatusconcate:

LOAD

[Customer ID]&[Contract ID]&Business as TS_TCB2,

//Business as TS_Business,

Upper(Status)as Commonstatus

FROM

DBa1.qvd]

(qvd);

Concatenate

sapstatusconcatinate:

load

num(Customer)&num([Contract No])&[Contract Type] as TS_TCB2,

[Contract Status] as Commonstatus

FROM

DB2.qvd]

(qvd);

talismaconcatinate:

Mapping

LOAD

          TS_TCB2,

          Commonstatus

Resident TMPTalismastatusconcate;

DROP table TMPTalismastatusconcate;

TMPCOMMONONTALISMA:

LOAD

     [Contract ID] as TS_O_Contract,

     [Customer ID]&[Contract ID]&Business as TS_TCB2,

    Upper(Status)as TS_O_STATUS,

    //[Customer ID]&[Contract ID]&Business as TS_TCB1,

   //ApplyMap('Talismastatusconcate',TS_TCB2)as Commonstatus1,

     [Contract ID]&Upper(Status) as TS_ONLYTALISMA

FROM

DBa1.qvd]

(qvd);

Inner Join(TMPCOMMONONTALISMA)

//SAPSecond:

LOAD

     num([Contract No]) as S_O_Contract,

    [Contract Status] as S_o_Status,

     num(Customer)&num([Contract No])&[Contract Type] as TS_TCB2,

     num(Customer)&num([Contract No])&[Contract Type] as TS_TCB,

      //ApplyMap('Talismastatusconcate',TS_TCB2)as Commonstatus1,

     num([Contract No])&[Contract Status] as S_ONLYSAP

FROM

DB2.qvd](qvd)Where NOT Exists('TS_ONLYTALISMA',num([Contract No])&[Contract Status]);

COMMONONTALISMA:

LOAD

TS_O_Contract,

TS_TCB2,

TS_TCB,

TS_O_STATUS,

TS_ONLYTALISMA,

S_O_Contract,

S_o_Status,

ApplyMap('talismaconcatinate',TS_TCB2) AS COMMONSTATUS1,

S_ONLYSAP

Resident TMPCOMMONONTALISMA;

DROP Table TMPCOMMONONTALISMA;