topic Re: how to calculate the field in script level....? in QlikView

how to calculate the field in script level....?

Mon, 02 Mar 2015 04:06:45 GMT

Hi All,

I am using the below expression in script level.But its not working what is the problem

I am calculating the Max month related data is store into current2015count field.

if(Created_date = date(max(Created_date),'MMM') ,[2015 Count 1],) as current2015count

Re: how to calculate the field in script level....?

timanshu — Mon, 02 Mar 2015 05:14:36 GMT

Hi Subbu,

As per Question, you are doing this by load statement.

If this is case, then you can't use aggregate functions in load statement without using group by clause.

But In your case using group by clause is not logical and will not either solve your problem.

So you first need to take a variable above this load statement and assign it value as max(Created_date) :

Let vMaxDate = max(Created_date).

Then in load statement write the variable instead of max(Created_date). like :

if(Created_date = date('$(vMaxDate)','MMM') ,[2015 Count 1],) as current2015count

This will solve your concern.

Re: how to calculate the field in script level....?

jagan — Mon, 02 Mar 2015 05:20:51 GMT

Hi,

You need to use Group by if you are using Max() in Load script, attach some sample data and let us know your required output.

Regards,

Jagan.

Re: how to calculate the field in script level....?

jonathandienst — Mon, 02 Mar 2015 05:59:23 GMT

I suggest you attach the load script for the table that contains the line in question.Sample data and a complete load script would also help.

Jonathan

Re: how to calculate the field in script level....?

avinashelite — Mon, 02 Mar 2015 06:25:30 GMT

can you please attach the app with sample data??

Re: how to calculate the field in script level....?

Mon, 02 Mar 2015 10:10:47 GMT

Hi,

this is my sample data.

Name	subject	Date	value Count	marks
mike	English	31-Jan-15	0	20
mike	maths	31-Jan-15	0	21
mike	science	31-Jan-15	0	24
mike	social	31-Jan-15	0	25
hari	English	31-Jan-15	2	22
hari	maths	31-Jan-15	2	24
hari	science	31-Jan-15	2	26
hari	social	31-Jan-15	2	27
john	English	31-Jan-15	4	18
john	maths	31-Jan-15	4	20
john	science	31-Jan-15	4	29
john	social	31-Jan-15	4	30
sai	English	31-Jan-15	1	20
sai	maths	31-Jan-15	1	22
sai	science	31-Jan-15	1	24
sai	social	31-Jan-15	1	26
mike	English	28-Feb-15	0	20
mike	maths	28-Feb-15	0	21
mike	science	28-Feb-15	0	24
mike	social	28-Feb-15	0	25
hari	English	28-Feb-15	2	22
hari	maths	28-Feb-15	2	24
hari	science	28-Feb-15	2	26
hari	social	28-Feb-15	2	27
john	English	28-Feb-15	4	18
john	maths	28-Feb-15	4	20
john	science	28-Feb-15	4	29
john	social	28-Feb-15	4	30
sai	English	28-Feb-15	1	20
sai	maths	28-Feb-15	1	22
sai	science	28-Feb-15	1	24
sai	social	28-Feb-15	1	26

and my expected design is:

Name	Baseline Valuecount	Current value count	Subject	Baseline Marks	Current Marks
mike	0	0	English	20	20
			maths	21	21
			science	24	24
			social	25	25
hari	2	2	English	22	22
			maths	24	24
			science	26	26
			social	27	27
john	4	4	English	18	18
			maths	20	20
			science	29	29
			social	30	30
sai	1	1	English	20	20
			maths	22	22
			science	24	24
			social	26	26

Plz help me in this part.

plz write the discussion in discussion window only.

Re: how to calculate the field in script level....?

Mon, 02 Mar 2015 10:11:25 GMT

Hi,

this is my sample data.

Name	subject	Date	value Count	marks
mike	English	31-Jan-15	0	20
mike	maths	31-Jan-15	0	21
mike	science	31-Jan-15	0	24
mike	social	31-Jan-15	0	25
hari	English	31-Jan-15	2	22
hari	maths	31-Jan-15	2	24
hari	science	31-Jan-15	2	26
hari	social	31-Jan-15	2	27
john	English	31-Jan-15	4	18
john	maths	31-Jan-15	4	20
john	science	31-Jan-15	4	29
john	social	31-Jan-15	4	30
sai	English	31-Jan-15	1	20
sai	maths	31-Jan-15	1	22
sai	science	31-Jan-15	1	24
sai	social	31-Jan-15	1	26
mike	English	28-Feb-15	0	20
mike	maths	28-Feb-15	0	21
mike	science	28-Feb-15	0	24
mike	social	28-Feb-15	0	25
hari	English	28-Feb-15	2	22
hari	maths	28-Feb-15	2	24
hari	science	28-Feb-15	2	26
hari	social	28-Feb-15	2	27
john	English	28-Feb-15	4	18
john	maths	28-Feb-15	4	20
john	science	28-Feb-15	4	29
john	social	28-Feb-15	4	30
sai	English	28-Feb-15	1	20
sai	maths	28-Feb-15	1	22
sai	science	28-Feb-15	1	24
sai	social	28-Feb-15	1	26

and my expected design is:

Name	Baseline Valuecount	Current value count	Subject	Baseline Marks	Current Marks
mike	0	0	English	20	20
			maths	21	21
			science	24	24
			social	25	25
hari	2	2	English	22	22
			maths	24	24
			science	26	26
			social	27	27
john	4	4	English	18	18
			maths	20	20
			science	29	29
			social	30	30
sai	1	1	English	20	20
			maths	22	22
			science	24	24
			social	26	26

Plz help me in this part.

plz write the discussion in discussion window only.

Re: how to calculate the field in script level....?

Mon, 02 Mar 2015 10:12:47 GMT

Hi,

this is my sample data.

Name	subject	Date	value Count	marks
mike	English	31-Jan-15	0	20
mike	maths	31-Jan-15	0	21
mike	science	31-Jan-15	0	24
mike	social	31-Jan-15	0	25
hari	English	31-Jan-15	2	22
hari	maths	31-Jan-15	2	24
hari	science	31-Jan-15	2	26
hari	social	31-Jan-15	2	27
john	English	31-Jan-15	4	18
john	maths	31-Jan-15	4	20
john	science	31-Jan-15	4	29
john	social	31-Jan-15	4	30
sai	English	31-Jan-15	1	20
sai	maths	31-Jan-15	1	22
sai	science	31-Jan-15	1	24
sai	social	31-Jan-15	1	26
mike	English	28-Feb-15	0	20
mike	maths	28-Feb-15	0	21
mike	science	28-Feb-15	0	24
mike	social	28-Feb-15	0	25
hari	English	28-Feb-15	2	22
hari	maths	28-Feb-15	2	24
hari	science	28-Feb-15	2	26
hari	social	28-Feb-15	2	27
john	English	28-Feb-15	4	18
john	maths	28-Feb-15	4	20
john	science	28-Feb-15	4	29
john	social	28-Feb-15	4	30
sai	English	28-Feb-15	1	20
sai	maths	28-Feb-15	1	22
sai	science	28-Feb-15	1	24
sai	social	28-Feb-15	1	26

and my expected design is:

Name	Baseline Valuecount	Current value count	Subject	Baseline Marks	Current Marks
mike	0	0	English	20	20
			maths	21	21
			science	24	24
			social	25	25
hari	2	2	English	22	22
			maths	24	24
			science	26	26
			social	27	27
john	4	4	English	18	18
			maths	20	20
			science	29	29
			social	30	30
sai	1	1	English	20	20
			maths	22	22
			science	24	24
			social	26	26

Plz help me in this part.

plz write the discussion in discussion window only.

currentcount means current month data baselinecount means jan month data it is fixed one

Re: how to calculate the field in script level....?

Mon, 02 Mar 2015 10:13:30 GMT

currentcount means current month data baselinecount means jan month data it is fixed one.

Re: how to calculate the field in script level....?

Mon, 02 Mar 2015 10:14:32 GMT

currentcount means current month data baselinecount means jan month data it is fixed one

Re: how to calculate the field in script level....?

avinashelite — Mon, 02 Mar 2015 13:12:29 GMT

Please check the attachment for the solution.

Re: how to calculate the field in script level....?

Mon, 02 Mar 2015 16:17:15 GMT

please write the discussion in discussion window ....i am using personal edition...

please help me.

Re: how to calculate the field in script level....?

avinashelite — Tue, 03 Mar 2015 05:12:24 GMT

Try like this:

Test:

LOAD Name,

subject,

Date,

[value Count],

marks

FROM

[http://community.qlik.com/thread/154524]

(html, codepage is 1252, embedded labels, table is @1);

NoConcatenate

Result:

load

Name,

subject,

Date,

[value Count],

sum(marks) as marks

Resident Test group by Name,

subject,

Date,

[value Count];

drop table Test;

Re: how to calculate the field in script level....?

mukesh24 — Tue, 03 Mar 2015 05:14:43 GMT

Hi Subbu,

When u r using aggregation function in Backend scripting use group by clause.

Regards,
Mukesh Chaudhari