topic Re: Re: Set Analysis with Character dimensions in QlikView

Set Analysis with Character dimensions

shyamcharan — Mon, 20 Oct 2014 00:36:43 GMT

Hi Experts,

Despite of looking at several example I still keep getting this question.

Set Analysis: Can we use the Set Analysis with Character dimensions?

If yes, can anyone please provide me information on any example that I can find.

This might be a very dumb question. I sincerely apologize for the guys who spend time on more important questions.

But your answers do help.

Thanks heaps in advance.

Regards,

Shyam.

Re: Set Analysis with Character dimensions

jagan — Mon, 20 Oct 2014 01:45:58 GMT

Hi,

What do you mean by Character Dimensions?

You are looking for this

=Sum({<Country={'India'}>} Sales)

=Count({<Product={'A'}>} OrderNo)

=Count({<A={'x', 'y', 'z'}>} OrderNo)

Regards,

Jagan.

Re: Set Analysis with Character dimensions

shyamcharan — Mon, 20 Oct 2014 01:51:17 GMT

Hi Jagan

Thank you for your reply.

Character Dimensions are the character field in the table.

If I had to display a value from a character field and use the SETS, how that would be?

Definitely, I cant use Sum() or Count() all the time right?

Thanks in advance.

Regards,

Shyam.

Re: Set Analysis with Character dimensions

jagan — Mon, 20 Oct 2014 01:55:31 GMT

HI,

Try this functions

=Only({<Product={'X'}>}DimensionName) -- To use this function you have only one possible value for the selectiosn, otherwise it will give you Null

=MaxString(DimensionName)

=MinString(DimensionName)

Check help file for detailed help.

Hope this helps you.

Regards,

Jagan.

Re: Re: Set Analysis with Character dimensions

shyamcharan — Mon, 20 Oct 2014 02:14:03 GMT

Hi Jagan,

I am getting error when I try with the functions you have mentioned.

Not sure what i am missing.

Can you please help me with that.

Basically I want the Equipment number to be shown for the Expiry date that are null.

Please find the attached file.

Thanks in advance.

Regards,

Shyam.

Re: Set Analysis with Character dimensions

jagan — Mon, 20 Oct 2014 02:25:15 GMT

Hi,

Try like this

Dimension: EQUIPMENT_NUMBER

Expression =Sum({<EXPIRY_DATE-={'*'}>} EQUIPMENT_COUNT)

Hope this helps you.

Regards,

Jagan.

Re: Set Analysis with Character dimensions

jyothish8807 — Mon, 20 Oct 2014 03:24:56 GMT

Hi Shyam,

Over here your field expiry date dosent have any value.If you put it in a list box its not giving any value.Fix that isue first then try like this:

Dimension:

=only({<EXPIRY_DATE-={'*'}>}EQUIPMENT_NUMBER)

expression:

Count(EQUIPMENT_ID)

Regards

Re: Re: Set Analysis with Character dimensions

shyamcharan — Mon, 20 Oct 2014 05:25:44 GMT

Hi Jyothish,

Thank you for your inputs. I have changed the column now from Expiry date to some other column that has data.

But still I get error in calculated dimension.

Can you please suggest further.

Thanks,

Shyam.

Re: Re: Set Analysis with Character dimensions

shyamcharan — Mon, 20 Oct 2014 05:27:00 GMT

Hi Jagan,

Thanks for your reply.

What if I donot have any numeric column to use it in SUM()?

How do i approach then?

Thanks in advance.

Regards,

Shyam.

Re: Set Analysis with Character dimensions

jonathandienst — Mon, 20 Oct 2014 05:32:21 GMT

Unfortunately you cannot find null values with set analysis. You have these options:

Use Sum(If()) expression, something like (if in a table or chart)

=MaxString(If(IsNull(ExpiryDate), EquipmentNumber))

Use NullAsValue to make the null value into an empty string, and then

=MaxString({<ExpiryDate = {''}>}, EquipmentNumber)

Set a null flag value during your load, like this:

LOAD ...

EquipmentNumber,

ExpiryDate,

If(IsNull(ExpiryDate), 1, 0) As NullDate,

...

Then use this set expression

=MaxString({<NullDate= {'1'}>}, EquipmentNumber)

HTH

Jonathan

Re: Set Analysis with Character dimensions

jyothish8807 — Mon, 20 Oct 2014 05:34:07 GMT

Hi Shayam,

Right now there is no null value for THE_DATE:

Try this in Dimension:

=if(isnull(THE_DATE)=-1,[EQUIPMENT_NUMBER],'')

Expression:

Count(Required field)

Regards

Re: Set Analysis with Character dimensions

jonathandienst — Mon, 20 Oct 2014 05:34:09 GMT

If you want it a a calculated dimension:

=Aggr(If(IsNull(ExpiryDate), EquipmentNumber), EquipmentNumber)

Re: Set Analysis with Character dimensions

jagan — Mon, 20 Oct 2014 05:49:35 GMT

Hi Shyam,

Can you explain with an example and your expected output. If you don't have numeric value then use

=Only({<Product={'X'}>}DimensionName)

=MaxString(DimensionName)

=MinString(DimensionName)

Regards,

Jagan.

Re: Re: Re: Set Analysis with Character dimensions

tresB — Mon, 20 Oct 2014 06:31:05 GMT

Just in support of Jonathan, and to come up with an example: