Creating new field based on existing fields

[sridhar_sigired]

Hi,

Can you please help me on this? Thanks for your time. Please find the attached example CALL data.

In my fact table, i have a CAL_ID field and Segment_ID Field.

One call may have multiple segments (Segment ID is unique) and can be answered by persons sitting at different vendor locations.

I need to create a field based on "Receiver_Location_Name", whether call had been transferred from other location or not?

Ex from sample data:

I have a call 4 with two segments 1004 (received from location ABC) and 1005 (transferred to location STV from ABC). I have also included field "Transferred" which actually need to be derived.

Is it possible to create this field for large dataset? if then what might be the quality of data, since we can not cross check the whole dataset.

Thanks a lot.

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD Cal_ID,

    Seg_ID,

    Receiver_Name as Receiver_Location_Name,

    Transferred

FROM

Data.xls

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

FinalTable:

LOAD *,

  If(Cal_ID = Peek('Cal_ID'), If(Peek('Receiver_Location_Name') = Receiver_Location_Name, 1, 0), 1) as New_Transferred

Resident Table

Order By Cal_ID, Seg_ID;

DROP Table Table;

[sridhar_sigired]

Thanks Sunny.

[sunny_talwar]

No problem