topic Re: The corresponding number in QlikView

The corresponding number

Mon, 20 Jan 2014 06:02:06 GMT

Hello,

There is a require to fulfill:

The data structure is like the below table:

The target report should be like this:

In the "Owner Total" column, it should show the corresponding records' number of "Sub Type Name".

Ie, using the above data structure for example, the record number of "17-25" should be 2, then the number 2 shown in the "Owner Total" column, and if the Sub Type Name value is "36-45", then the number 3 should be shown in the "Owner Total" column.

So who can tell me how should I do? It's urgent. Please help me. Thanks very much in advance.

Best regards,

CoCo

Re: The corresponding number

Mon, 20 Jan 2014 06:10:51 GMT

Is it possible to attach sample app or data.

Where is the data for "Type Name" and "Sub Type Name"

Re: The corresponding number

Mon, 20 Jan 2014 07:24:01 GMT

For soem reason, your Account.qvd is blank, can you provide with sample data and what is the Key/Link between Account and Customer profile report?

Re: Re: The corresponding number

Mon, 20 Jan 2014 07:56:58 GMT

Hi,

Maybe is the problem of the file path. Please try again.

Thanks very much.

CoCo

Re: Re: The corresponding number

Mon, 20 Jan 2014 07:58:50 GMT

This is the new qvw file.

Re: The corresponding number

Mon, 20 Jan 2014 08:33:43 GMT

Hello everyone,

It's urgent to solve this issue. Can anyone else help me ?

Thank you!

CoCo

Re: The corresponding number

hic — Mon, 20 Jan 2014 08:41:18 GMT

The above report cannot be made that way in QlikView, because you are mixing metadata with data. "Type" and "Type Name" are metadata - they are fields that are not found in the real data (and cannot be inferred from data either). But the "Sub Type Name" contains real data values, but from different fields.

What you should do, is to create one pivot table per "Type Name" value and organize it on the screen so that it resembles the structure you want. The two charts below are equivalent to your "Age" and your "Education"

HIC

Re: The corresponding number

Mon, 20 Jan 2014 09:00:23 GMT

Hi Henric,

Thanks too much. I'll have a try according to your suggestions.

But the above report's structure is the customer required, so do you have any other methods to fulfill the requirement?

Would you please think again?

Thanks too much.

CoCo

Re: The corresponding number

tresB — Mon, 20 Jan 2014 09:41:53 GMT

Are you counting the [Sub Type Name] based on Name (Lily1, Lily2, ...) field? If so, it has to be there in your sample qvw file.

Re: Re: The corresponding number

Mon, 20 Jan 2014 10:15:17 GMT

Hi,

Henric is absolutely correct, but for client requirements you can check below attachment. It may help you

Re: The corresponding number

flipside — Mon, 20 Jan 2014 11:10:27 GMT

Hi,

Linking your data is the proper way to go, or flatten the data as the example Nagaraj has given, although this expression may be a solution, but it's a bit cumbersome and I can't see __c suffixed fields for each [Sub Type Name] test ...

=sum(
if(Age_Range__c=[Sub Type Name],1,
if(Marital_Status__c=[Sub Type Name],1,

... repeat for each test

,0)
))))) {etc etc.}

flipside

Re: The corresponding number

Tue, 21 Jan 2014 02:22:44 GMT

Hello Nagaraj,

Actually, my data is the same with the attachment you provide for me.

And as I'm using the "Personal Edition", so I can't open the qvw file you give me .

If it's convenient for you, can you please provide the specific steps what I should do for me?

Thanks very much!!!

CoCo

Re: The corresponding number

Tue, 21 Jan 2014 02:44:57 GMT

Hi flipside,

Do you know how should I do to link my data?

Thanks very much.

CoCo

Re: Re: The corresponding number

Wed, 22 Jan 2014 08:51:21 GMT

Hi CoCo,

Sorry for the delay in replay, i was busy with my project.

I have attached the script for you.

Basically you have to fattern the CPR data and create key using concate and create the same key in Account.

Then with If condition you can get "Sub Type Name"