Using multiple fields to determine another field

[#1]

Hi

I have three fileds in my sql table that I extracted. These three tables combined, will actually determine an new field and I need to find the best way to do this.

For example.

My three fields is Interaction1, Interaction2 and Interaction3. The rules that will determine my fourth field, called Interaction Type, are as follows:

• If Interaction1 = Y, then the Interaction Type = Online (This is the first check and it doesn't matter what Interaction2 and 3 is)
• If Interaction2 = In Store, then the Interaction Type = In Store
• If Interaction3 = Y, then the Interaction Type = In Store
• Else the Interaction Type = Other

I tried to do this in a nested if statement when I populate my table, but I am getting an error stating that it doesn't recognise the syntax near the if?? See the script below:

[DEAL INFO]:

SQL

SELECT CaptureUser,

"Collection_Type",

DealCompleteDate,

DealSequenceNo,

DealStartDate,

DealType AS [Deal Type],

Interaction1,

"Call_Id" as Interaction2,

CaptureFranchise as Interaction3,

if(Interaction1='Y','Online',if(Interaction2='In Store','In Store',if(Interaction3='Y','In Store','Other')))

FROM CactusDataStore.AdHoc."QV_DealTimeCTQ_DealData";

So, I went to plan B and tried to make an Inline table with the three interactions and based on the combination it should pick up the interaction type as follows:

Star

LOAD

* INLINE [ is *;

[INTERACTION TYPES]:

Interaction1, Interaction2, Interaction3, Interaction Type

Y,*,*,Online

N,In Store,*,In Store

N,*,Y,In Store

N,*,*,Other

]

;

This is almost working, but I realized that it doesn't do all the data reductions to all the tables?? I think it has something to do with the other fields that are still seen as ALL values?

Please assist. . .

[maneshkhottcpl]

Hi,

Use the precedent load

like

Load

CaptureUser,

"Collection_Type",

DealCompleteDate,

DealSequenceNo,

DealStartDate,

DealType AS [Deal Type],

Interaction1,

"Call_Id" as Interaction2,

CaptureFranchise as Interaction3,

if(Interaction1='Y','Online',if(Interaction2='In Store','In Store',if(Interaction3='Y','In Store','Other'))) as NewFiled;

sql select * from CactusDataStore.AdHoc."QV_DealTimeCTQ_DealData";

use this

and let me know the status.

[#3]

Hi Manesh

Thank you, it did help to a certain extend, but I am actually also applying a Map to the to interactions before I create the table, and this is now causing the if statement to not work properly.

I tried to do a join to the table afterwards just for the if, but then my computer bombs out and I get an error to say that it is out of vertual memory.

Is there maybe a better way of doing it?

See below my script:

MAP

MAP

SQL

JOIN

([DEAL INFO])

LOAD

Resident

[DEAL INFO];

if(Interaction1='Y','Online',if(Interaction2='In Store','In Store',if(Interaction3='Y','In Store','Other'))) as [Interaction Type] SELECT CaptureUser as [Sales Person],

CaptureFranchise,

"Delivery Method",

DealCompleteDate,

DealSequenceNo AS TimeDeal,

DealSequenceNo as WorkflowDeal,

DealStartDate,

"Deal Type",

Interaction1,

Interaction2,

Interaction3

FROM CactusDataStore.AdHoc."QV_DealTimeCTQ_DealData";

Interaction3 using OUTLETSMAP;

[DEAL INFO]:

Interaction2 using CALLID;

[#4]

Sorry, I see my script is a bit weird. . . See below

MAP

MAP Interaction3 using OUTLETSMAP;

[DEAL INFO]:

SQL

JOIN

([DEAL INFO])

LOAD

Resident

[DEAL INFO];

if(Interaction1='Y','Online',if(Interaction2='In Store','In Store',if(Interaction3='Y','In Store','Other'))) as [Interaction Type] SELECT CaptureUser as [Sales Person],

CaptureFranchise,

"Delivery Method",

DealCompleteDate,

DealSequenceNo as TimeDeal,

DealSequenceNo as WorkflowDeal,

DealStartDate,

"Deal Type",

Interaction1,

Interaction2,

Interaction3

FROM CactusDataStore.AdHoc."QV_DealTimeCTQ_DealData";

Interaction2 using CALLID;

[#5]

MAP

SQL

JOIN

([DEAL INFO])

LOAD

Resident

[DEAL INFO];

if(Interaction1='Y','Online',if(Interaction2='In Store','In Store',if(Interaction3='Y','In Store','Other'))) a[Interaction Type] SELECT CaptureUser as [Sales Person],

CaptureFranchise,

"Delivery Method",

DealCompleteDate,

DealSequenceNo as TimeDeal,

DealSequenceNo as WorkflowDeal,

DealStartDate,

"Deal Type",

Interaction1,

Interaction2,

Interaction3

FROM CactusDataStore.AdHoc."QV_DealTimeCTQ_DealData";

Interaction2 using CALLID;

MAP

Interaction3 using OUTLETSMAP;

[DEAL INFO]:

[amars]

Use

your code
[\code]
or first paste it in Notepad & then copy and paste it in forums
 </body>

[#7]

MAP



 Interaction2 using
 CALLID;
MAP



 Interaction3 using
 OUTLETSMAP;
[DEAL INFO]:
SQL




 SELECT CaptureUser as [Sales Person],
CaptureFranchise,
"Delivery Method",
DealCompleteDate,
DealSequenceNo as TimeDeal,
DealSequenceNo as WorkflowDeal,
DealStartDate,
"Deal Type",
Interaction1,
Interaction2,
Interaction3
FROM CactusDataStore.AdHoc."QV_DealTimeCTQ_DealData";
JOIN


 ([DEAL INFO])
LOAD



 if(Interaction1='Y','Online',if(Interaction2='In Store','In Store',if(Interaction3='Y','In Store','Other'))) as
[Interaction Type]
Resident


 [DEAL INFO];
[\code]</body>

[#8]

MAP Interaction2 using CALLID;

MAP Interaction3 using OUTLETSMAP;

[DEAL INFO]:
SQL SELECT CaptureUser as [Sales Person],
CaptureFranchise,
"Delivery Method",
DealCompleteDate,
DealSequenceNo as TimeDeal,
DealSequenceNo as WorkflowDeal,
DealStartDate,
"Deal Type",
Interaction1,
Interaction2,
Interaction3
FROM CactusDataStore.AdHoc."QV_DealTimeCTQ_DealData";
JOIN ([DEAL INFO])
LOAD if(Interaction1='Y','Online',if(Interaction2='In Store','In Store',if(Interaction3='Y','In Store','Other'))) as [Interaction Type]
Resident [DEAL INFO];

[johnw · Accepted solution]

You need to join on something, typically the key value for the table. Since you didn't, it's giving you a cartesian product and running out of memory. Alternatively, use preceeding loads as suggested before. You might need to use applymap() instead of MAP in that case, just to make certain the map is applied BEFORE you start checking the values (I'm not certain when the MAP is applied). Something like this:

[DEAL INFO]:
LOAD *
,if(Interaction1='Y' ,'Online'
,if(Interaction2='In Store','In Store'
,if(Interaction3='Y' ,'In Store'
,'Other'))) as "Interaction Type"
;
LOAD
CaptureUser as "Sales Person"
,CaptureFranchise
,"Delivery Method"
,DealCompleteDate
,DealSequenceNo as TimeDeal
,DealSequenceNo as WorkflowDeal
,DealStartDate
,"Deal Type"
,Interaction1
,applymap('CALLID',Interaction2) as Interaction2
,applymap('OUTLETSMAP',Interaction3) as Interaction3
;
SQL SELECT
CaptureUser
,CaptureFranchise
,"Delivery Method"
,DealCompleteDate
,DealSequenceNo
,DealStartDate
,"Deal Type"
,Interaction1
,Interaction2
,Interaction3
FROM CactusDataStore.AdHoc."QV_DealTimeCTQ_DealData"
;

[#10]

Thanks

It worked!!