topic Small Help in Script require in QlikView

Small Help in Script require

pra_kale — Tue, 29 Sep 2015 18:11:25 GMT

Hi,

In a script I require a small help. I am given below a sample table in that Policy numbers are repeated as appears in different different months with Premium. But in script only I want a unique policies with summation of their premium.

Original Data

Policy_no	Premium	Year	Month	Channel
12345678	-10000	2010	1	Agency
10002345	12345	2010	2	Banka
12345678	23000	2010	2	Agency
12345678	25000	2010	3	Agency
10002345	5000	2010	2	Banka
12389401	12000	2010	3	Alernate
12389401	10000	2010	2	Alernate
12389401	10000	2010	2	Alernate

Out-put require in a script it self like below, to use it further

Policy_no	Channel	Premium
10002345	Banka	17345
12345678	Agency	38000
12389401	Alernate	32000

I am writing below given script to load data but I don't know how to bring data in this order (i.e. out-put) to use it further, as I don't want multi pal policy numbers.

Directory;

LOAD Policy_no,

Premium,

Year,

Month,

Channel

FROM

sample.xlsx

(ooxml, embedded labels, table is Sheet1);

Thanks in advance.

Re: Small Help in Script require

maxgro — Tue, 29 Sep 2015 18:25:34 GMT

replace inline table in my script with excel

[Original Data]:

load * inline [

Policy_no, Premium, Year, Month, Channel

12345678, -10000, 2010, 1, Agency

10002345, 12345, 2010, 2, Banka

12345678, 23000, 2010, 2, Agency

12345678, 25000, 2010, 3 ,Agency

10002345, 5000, 2010, 2, Banka

12389401, 12000, 2010, 3, Alernate

12389401, 10000, 2010, 2, Alernate

];

Final:

load Policy_no, Channel, sum(Premium)

Resident [Original Data]

group by Policy_no, Channel;

drop table [Original Data];

Re: Small Help in Script require

Tue, 29 Sep 2015 18:30:21 GMT

will they ever want to see the different amounts for each policy by month?

If they just want a lump sum, the response you recieved will provide that. You can also look at loading like you did but build a pivot table by policy number, month and premium - if you do not expand the month, the premium will be the aggregate of all months for that policy