topic Using multiple fields to determine another field in QlikView

Using multiple fields to determine another field

Fri, 25 Feb 2011 08:53:46 GMT

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. . .

Using multiple fields to determine another field

maneshkhottcpl — Fri, 25 Feb 2011 09:37:11 GMT

Hi,

Use the precedent load

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.

Using multiple fields to determine another field

Fri, 25 Feb 2011 13:31:50 GMT

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

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;

Using multiple fields to determine another field

Fri, 25 Feb 2011 13:38:27 GMT

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;

Using multiple fields to determine another field

Fri, 25 Feb 2011 13:42:11 GMT

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]:

Using multiple fields to determine another field

amars — Fri, 25 Feb 2011 13:42:45 GMT

Use


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

Using multiple fields to determine another field

Fri, 25 Feb 2011 13:50:25 GMT























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>

Using multiple fields to determine another field

Fri, 25 Feb 2011 13:51:43 GMT

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];

Using multiple fields to determine another field

johnw — Fri, 25 Feb 2011 23:03:04 GMT

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"
;

Using multiple fields to determine another field

Mon, 28 Feb 2011 07:26:34 GMT

Thanks

It worked!!