topic Re: Urgent plz help - Set analysis in QlikView

Urgent plz help - Set analysis

Wed, 28 Mar 2012 07:14:15 GMT

Hi All,

I have a field status which has values "Buy","Sell" and "Hold".Due to a join with another table nulls are coming in the status field.I want to capture them using set analysis how do i do that..I am using valuelist function as a dimension so need to group the nulls in the expression...

Any help is greatly appreciated..

Thnaks in advance.. Plz let me know if yu need any help..

Urgent plz help - Set analysis

sivarajs — Wed, 28 Mar 2012 07:27:21 GMT

u want to capture null values???

sivaraj s

Urgent plz help - Set analysis

CELAMBARASAN — Wed, 28 Mar 2012 07:31:27 GMT

Hi,

Use following set analysis to get null status Sales.

Sum({$<Status-={*}>} Sales)

Celambarasan

Urgent plz help - Set analysis

Wed, 28 Mar 2012 07:32:30 GMT

Hi lavanya,

Write the expression as:

{<FieldStatus={}>}. This may help you to capture all the null values of your Status Field.

Regards,

Pranav

Re: Urgent plz help - Set analysis

Wed, 28 Mar 2012 07:37:12 GMT

Hi,

I am sorry that is not working.. Just for ur info.. I have grouped the null values coming in the field as "Not defined" in the script.. The nulls that I am speaking are coming due to outer join with another table.. I need to capture them as well so plz let me know if u have any suggestions..

Thanks

Re: Urgent plz help - Set analysis

Wed, 28 Mar 2012 07:38:06 GMT

Hi,

Sorry that is not working ..

Re: Urgent plz help - Set analysis

sivarajs — Wed, 28 Mar 2012 07:48:19 GMT

try this

{$<field={' '}>}

Sivaraj S

Re: Urgent plz help - Set analysis

CELAMBARASAN — Wed, 28 Mar 2012 08:41:39 GMT

Hi,

You grouped null values in the name of not defined within the load script right?So you can use

Sum({$<Status-={'Not Defined'}>} Sales)

Celambarasan

Re: Urgent plz help - Set analysis

Thu, 29 Mar 2012 07:23:24 GMT

Hi,

The Null values are not coming from excel they are introduced due to a join with another table. I need to capture them.. Any suggestion please let me know..

Thanks

Re: Urgent plz help - Set analysis

CELAMBARASAN — Thu, 29 Mar 2012 07:28:48 GMT

Hi,

Explain little more briefer about the join between 2 tables and how the null value occurs?

Celambarasan

Re: Urgent plz help - Set analysis

Thu, 29 Mar 2012 15:18:50 GMT

Hi,

Sorry here are the details. I have a master project table (Table1) where all the ProjectId's will be present.

Tab2 and Tab3 are outer joined based on EMPKEY in order to maintain all the data and finally outer joined with the Tab1 using the ProjectId.I have a status field coming from Tab2(Excel ) which has some blanks.Due to outer joins I see nulls coming in the status field. I need to make a bar chart against all the statuses.I had faces a problem while capturimg the null values..

Hope I have given you the info.. Please do let me know if u need any info..

Thanks

Urgent plz help - Set analysis

Thu, 29 Mar 2012 17:17:27 GMT

So far, the only solution I found in one of my apps was to get the total substracted the not null values {1} - {'*'}

Another solution could be to make a resident load from your joinned table and asign values to the null values ('undefinded') to use in set analysis.

Urgent plz help - Set analysis

CELAMBARASAN — Fri, 30 Mar 2012 04:43:43 GMT

Hi,

Your going to use Status as dimension where you going to group it under not defined am i right?

if so try with calculated dimension

if(len(Trim(Status))>0,Status,'Not Defined')

Celambarasan

Urgent plz help - Set analysis

Fri, 30 Mar 2012 04:50:43 GMT

Why not simplify the problem by changing the loading script to handle the NULL value?

Urgent plz help - Set analysis

Fri, 30 Mar 2012 05:18:59 GMT

the field in which null records are coming is to be used as expression or in dimension.

Re: Urgent plz help - Set analysis

Fri, 30 Mar 2012 05:37:58 GMT

You can use your expression as

=if(NULL.rec2='','NV',

if(NULL.rec1='', 'ND'

,NULL.rec1

))

if we are using rec2 as expression and rec1 as dimension then,

No Value (NV) for expression which are null,

Not Defined for dimension ehich are null,

forther you can provide some default value to NV experssion

By editing Expression:

if(NULL.rec2='',0,sum(NULL.rec2))

Refer To attached file:

Re: Urgent plz help - Set analysis

Sat, 31 Mar 2012 06:15:31 GMT

Hi,

Not excatly I have a chart where i use the Valuelist to create the dimension (Eg. values in the valuelist are budget,forecast etc..) in the expression I have to differentiate the different statuses present in the "System Investment Status" field.. I was able to write expression for other available statuses but failed to catch up the null values...

Hope this is clear now...

Thanks

Urgent plz help - Set analysis

CELAMBARASAN — Mon, 02 Apr 2012 04:33:58 GMT

Hi,

Can you give me the example data and script you used?

Celambarasan

Re: Urgent plz help - Set analysis

Mon, 02 Apr 2012 04:45:49 GMT

Hi Gm. I will try to generate some sample data and attach..

Thanks

Re: Urgent plz help - Set analysis

Mon, 02 Apr 2012 06:07:20 GMT

Hi,

Just a small clarification.. I have a stacked chart where I use valuelist as Dimension.. and four expressions(for fdifferent four statues) using set analysis.. When I filter another bar chart with one of the status this Stacekd chart is not geting filter.. I think it should be because of the set analysis used in the expressions.. If you have any suggestions please let me know...

Thanks