topic Re: Implement the query in QlikView

Implement the query

arulsettu — Thu, 12 Feb 2015 04:08:35 GMT

Hi i am trying to implement the following query

select pdr_prod_code, count(1),

sum(decode(pdr_status, 'R', 1, 0)) from t_pol_due_for_ren

where trunc(pdr_pol_to_dt) between '01-DEC-2014' and '31-DEC-2014'

group by pdr_prod_code

in line chart. month and year as dimensions

please suggest me something its urgent...

thank you

Re: Implement the query

PrashantSangle — Thu, 12 Feb 2015 04:18:16 GMT

Hi,

In script try like

Load pdr_prod_code,

sum(if(pdr_status='R',1,0)) as Sum_pdr_Status,

from t_pol_due_for_ren

where Date(pdr_pol_to_dt,'DD-MMM-YYYY')>='01-Dec-2014' and Date(pdr_pol_to_dt,'DD-MMM-YYYY')<='31-Dec-2014'

group by pdr_prod_code;

If you want to write set analysis using above condition then try like

sum(if(pdr_status='R' and pdr_pol_to_dt>='01-Dec-2014' and pdr_pol_to_dt<='31-Dec-2014',1,0))

Regards

Re: Implement the query

arulsettu — Thu, 12 Feb 2015 04:31:21 GMT

max i used below code in script now what to write in chart

Load pdr_prod_code,

sum(if(pdr_status='R',1,0)) as Sum_pdr_Status,

from t_pol_due_for_ren

where Date(pdr_pol_to_dt,'DD-MMM-YYYY')>='01-Dec-2014' and Date(pdr_pol_to_dt,'DD-MMM-YYYY')<='31-Dec-2014'

group by pdr_prod_code;

thank you

Re: Implement the query

PrashantSangle — Thu, 12 Feb 2015 04:36:30 GMT

Hi,

As you said you have month and Year as dimension then you have dimension and

in expression what do you want to show??

if you want to show Sum_pdr_Status then use it as expression.

Regards

Re: Implement the query

arulsettu — Thu, 12 Feb 2015 04:40:52 GMT

hi max i have to show like this

In y axis have to show in ratio

thanks

Re: Implement the query

arulsettu — Thu, 12 Feb 2015 04:56:59 GMT

any ideas?

Re: Implement the query

arulsettu — Thu, 12 Feb 2015 05:07:36 GMT

hi max

can you suggest based on the image

thanks

Re: Implement the query

krishna20 — Thu, 12 Feb 2015 05:25:16 GMT

Hi Arul,

If you are struggling to write an expression for this. Simply follow these steps you can achieve what you want

Step1 :

Prod_Code_Cal:

select pdr_prod_code, count(1),

sum(decode(pdr_status, 'R', 1, 0)) Prod_sum from t_pol_due_for_ren

where trunc(pdr_pol_to_dt) between '01-DEC-2014' and '31-DEC-2014'

group by pdr_prod_code

Store Prod_Code_Cal into [../QVD/Prod_Code_Cal.qvd];

drop table Prod_Code_Cal;

Step2 :

Upload this QVD and associate to t_pol_due_for_ren and Prod_Code table.

Step3:

Take Line chart with your dimensions and expression .

Note : Please notice on Prod_sum.you need to store this result in a column.Then, only you can use this value in expression on front End.

Regards

Krishna

Re: Implement the query

arulsettu — Thu, 12 Feb 2015 05:39:46 GMT

max it showing invalid expression plz suggest

Re: Implement the query

arulsettu — Thu, 12 Feb 2015 05:49:52 GMT

not getting the result

Re: Implement the query

arulsettu — Thu, 12 Feb 2015 06:02:44 GMT

can any one help me on this issue

Re: Implement the query

arulsettu — Thu, 12 Feb 2015 06:35:08 GMT

any one plz?

Re: Implement the query

jagan — Thu, 12 Feb 2015 08:13:14 GMT

Hi ARul,

Check thsi expression

sum({<PDR_STATUS={'R'}>}PDR_PROD_CODE)/sum({<PDR_STATUS={'R'}>} TOTAL <Year>PDR_PROD_CODE)

Regards,

Jagan.

Re: Implement the query

arulsettu — Thu, 12 Feb 2015 08:18:54 GMT

thanks

but result not similar to above image and need to show y axis in percentage

Re: Implement the query

jagan — Thu, 12 Feb 2015 08:21:08 GMT

Hi,

Check this file, is this you are expecting?

If not explain with an example.

Regards,

Jagan.

Re: Implement the query

arulsettu — Thu, 12 Feb 2015 08:27:30 GMT

what i need is count of pro_status =R/ product code in percentage

Re: Implement the query

arulsettu — Thu, 12 Feb 2015 08:40:09 GMT

hi Jagan need exactly like this

status R / Total count

group by product

Re: Implement the query

jagan — Thu, 12 Feb 2015 08:42:11 GMT

Hi,

Can you explain with some sample data and your expected output.

Regards,

Jagan.

Re: Implement the query

arulsettu — Thu, 12 Feb 2015 08:47:57 GMT

in above qvw fields called

pdr_status in this i have to take pdr_status='R'/Count of pdr_prod_code group by pdr_prod_code

have to show in percentage

Re: Implement the query

jagan — Thu, 12 Feb 2015 08:54:39 GMT

Hi,

PFA file, hope it helps you.

Regards,

Jagan.