topic Set Analysis in QlikView

Set Analysis

Wed, 05 May 2010 08:45:48 GMT

Can please Some one share a notes on Set Analysis..

Thanks

Ankit

Set Analysis

spsrk_84 — Wed, 05 May 2010 10:34:21 GMT

Hi,

Which version of Qlikview you are using?

If you are using Qv 9 then go to help and just type set analysis u will find lot of example related to Set Analysis.I feel those examples will definitely help you to understand what it is.........

and practise few of them to get hands on experience and also try to understand how set analysis expression are written in few of the QlikView examples available when u open QlikView

Regards,

Ajay

Set Analysis

Wed, 05 May 2010 10:48:18 GMT

HI Ajay,

I am using Qlikview 8.5.. and i am not getting enough examples through wihich i can practise..

Please help me if u have any solution or docs for this.

Thanks,

Ankit

Set Analysis

spsrk_84 — Thu, 06 May 2010 05:31:23 GMT

Hi,

Download the Qv 9 Personal Edition and install it in some other machine and as i said earlier in Qv 9.0 u can go to help and search for Set Analysis in INdex .I hope this will help u alot and meanwhile i will try to send any douments related to that.........

Regards

Ajay

Set Analysis

Thu, 06 May 2010 05:52:14 GMT

Hi,

Thanks Ajay, will do as per your suggestion, meanwhile if u find any doc related to this and one more request i need a documnet related to Mapping Load., please do help me out.

Thanks

Ankit

Set Analysis

Thu, 06 May 2010 06:00:44 GMT

Here's a document presented by Tom Mackay regarding Set Analysis.

Set Analysis

spsrk_84 — Thu, 06 May 2010 07:40:12 GMT

Hi ,

Anthony Thanks alot . The document you shared will be very much helpfull to everyone.....

Ankit

Have u gone through the doc shared by Anthony ,Hope your problem is solved...

Regards

Ajay

Set Analysis

Thu, 06 May 2010 08:07:54 GMT

Thanks a lot.. it's realy helpful.

Ajay,

Thanks for your support..

I am sorry Guys but i am new to this so asking more. do u have such types of notes realted to MApping Load. and have a query on this too. As i use mapping load with simple 2 tables and i have read that its and alternative option of joins. but how to use left.right join in this??

Please help my query using Mapping Load instead of using JOIN, I have 2 table

Table1:

IR_DPD	no of Accts
0 - 29 DPD	24,443
30 - 59 DPD	11,770
60 - 89 DPD	8,426
90 - 119 DPD	8,001
120 - 149 DPD	1
180 - 209 DPD	1
240 - 269 DPD	1
390 - 419 DPD	2
Current	317,066
GWO	3,938
NCL	68,928

Table 2:

IR_DPD	FullPOS	POS
0 - 29 DPD	631,626,108	1
30 - 59 DPD	303,230,518	2
60 - 89 DPD	215,404,729	3
90 - 119 DPD	203,847,230	4
120 - 149 DPD	320,946	5
180 - 209 DPD	376,862	6
240 - 269 DPD	914,550	7
390 - 419 DPD	497,457	8
Current	12,394,438,430	9

Now i want the Table 2 data should join into Table1(using mapping load). not Table 1 to table 2 and the output i should get is:

IR_DPD	no of Accts	FullPOS	POS
0 - 29 DPD	24,443	631,626,108	1
30 - 59 DPD	11,770	303,230,518	2
60 - 89 DPD	8,426	215,404,729	3
90 - 119 DPD	8,001	203,847,230	4
120 - 149 DPD	1	320,946	5
180 - 209 DPD	1	376,862	6
240 - 269 DPD	1	914,550	7
390 - 419 DPD	2	497,457	8
Current	317,066	12,394,438,430	9
GWO	3,938	-	-
NCL	68,928	-	-

Thanks

Ankit

Set Analysis

Thu, 06 May 2010 08:19:30 GMT

Thanks Anthony, ur notes are very useful and solving my queries..

Set Analysis

Thu, 06 May 2010 08:19:51 GMT

Use codes as under

Map1;

mapping LOAD * INLINE [

x,y

0 - 29 DPD,24443

30 - 59 DPD,11770

60 - 89 DPD,8426

90 - 119 DPD,8001

120 - 149 DPD,1

180 - 209 DPD,1

240 - 269 DPD,1

390 - 419 DPD,2

Current,317066

GWO,3938

NCL,68928];

map no of Accts using map1;

Now load your table and add a column o fof Accts to it whihc has the same values as IR
_DPD

that should work

Set Analysis

Thu, 06 May 2010 08:47:04 GMT

Didn't got what you are saying.

Set Analysis

Thu, 06 May 2010 08:54:48 GMT

Add the mapping table as suggested above

then load your table which has the data as IR_DPD FullPOS & POS in addition to that add another column to the data and name it as no of Accts and the values in it would be same as in the IR_DPD column

in this case the code above would work

But couldnt get you why don't you just join the two tables - is easier

Set Analysis

spsrk_84 — Thu, 06 May 2010 11:22:54 GMT

Hi Ankit,

As Umang said join is the right thing here and i feel it will be the better one ,Since in this scenario Join will results better performance

and second thing like in the above scenario u r performing Join Operation only and there is no use of Mapping at all.......

Regards,

Ajay

Set Analysis

Thu, 06 May 2010 11:48:42 GMT

HI Ajay,

Thanks for the reply, i know join would be the option,but we were told to use MApping load isntead of Join in the above example so i was not aware of it and not much idea of Mapping so i had requested the code for Mapping load to get the desired output. And want to learn something related to Mapping Load.

Thanks

Ankit

Set Analysis

Thu, 13 May 2010 12:04:29 GMT


MAP_K:
Mapping LOAD * INLINE [
 IR_DPD, no of accounts
 0 - 29 DPD, "24,443"
 30 - 59 DPD , "11,770"
 60 - 89 DPD, "8,426"
 90 - 119 DPD , "8,001"
];
P:
LOAD * INLINE [
 IR_DPD, FullPOS, POS
 0 - 29 DPD , "631,626,108 ", 1
 30 - 59 DPD, "303,230,518", 2
 60 - 89 DPD , "215,404,729", 3
];

Final:

Load IR_DPD,
applymap('MAP_K',IR_DPD) as [no of accounts],
FullPOS,
POS
Resident P;
Drop Table P;

Try the above code,it will definitely work. I have dropped the table because it is creating a synthetic table and we dont need to have table P because its almost the exact copy of final without the field 'no of accounts'.

Set Analysis

Fri, 14 May 2010 08:34:44 GMT

Thanks a lot it's working....