topic Re: Help on Calculated Dimension in QlikView

Help on Calculated Dimension

Anonymous — Wed, 26 Oct 2016 12:33:03 GMT

Hi All,

I have a requirement explained as below,

In my table I have columns like ID and Code and using this code I have created two calculated dimensions as below,

Load ID,

If(Match(Code,'A','B'),Code) as 'AB',

If(Code='C',Code) as 'C'

From table.qvd(qvd);

Now I am getting result as

ID AB C

123456 Code1 -

123456 - Code2

123456 - -

Multiple rows for a single ID. But I would like to get a single row for a ID as below,

ID AB C

123456 Code1 Code2

Can anyone help me to implement the same?

Thanks in advance.

Regards,

Leni Balakrishnan

Re: Help on Calculated Dimension

sunny_talwar — Wed, 26 Oct 2016 12:47:45 GMT

Can you share raw data of how it looks? I want to see how Code field look like?

Re: Help on Calculated Dimension

rubenmarin — Wed, 26 Oct 2016 12:58:09 GMT

Hi Leni, from that simple table you can create the expected result using a group by:

Result:

NoConcatenate

LOAD ID,

MinString(AB) as AB,

MinString(C) as C

Resident YourPreviousTable Group By ID;

This will keep only one [AB] and value for each ID (the first one alphabetically sorted). If Codes are numbers you can use Min() instead of MinString()

Re: Help on Calculated Dimension

Anonymous — Thu, 27 Oct 2016 04:29:14 GMT

Hi Sunny,

Thanks for your reply.

Please find the attached sample data and sample qvw file.

In the qvw file I have showed how I m getting the data currently. But I want the result to be shown in a single row.

Please let me know if you need further more details.

Regards,

Leni Balakrishnan

Re: Help on Calculated Dimension

Anonymous — Thu, 27 Oct 2016 04:41:40 GMT

Thanks for your reply Ruben.

I just tried your solution with my dummy data and it is giving as I expected but I found data missing when I used the same.

Like,

For the same ID if I have Codes as MBNR,NAWD,FOOC,ABUA

for the calculation If(Match(Code,'NAWD','MBNR'),Code) as [NAWD/MBNR Codes] it should return value for both MBNR & NAWD

but once I used your solution it is showing data only for the first occurrence and not for the second.

Attached screenshot for your reference.

Please help me to resolve the issue.

Regards,

Leni Balakrishnan

Re: Help on Calculated Dimension

tamilarasu — Thu, 27 Oct 2016 05:33:57 GMT

Hi Leni,

I have attached a solution from my side.

Re: Help on Calculated Dimension

Anonymous — Thu, 27 Oct 2016 07:20:39 GMT

Hi Tamil,

Thank you for your solution.

I try to implement the same in my actual code. If any doubts will get back to you

I will mark your answer as Correct once it worked out in my actual code

Regards,

Leni Balakrishnan

Re: Help on Calculated Dimension

rubenmarin — Thu, 27 Oct 2016 07:33:19 GMT

Hi Leni, yes, it was intented as i advised: "This will keep only one [AB] and value for each ID (the first one alphabetically sorted)."

If you want all values concatenated use Tamil's solution, If you want NBNR and NAWD in different rows you can use something like:

LOAD ID, Subfield([NAWD/MBNR], ',') as [NAWD/MBNR], [FOOC Code]; //This LOAD reads from below LOAD

LOAD ID, Concat([NAWD/MBNR], ',') as [NAWD/MBNR], Minstring([FOOC Code]) as [FOOC Code]

FROM... Group by ID;

Re: Help on Calculated Dimension

Anonymous — Thu, 27 Oct 2016 09:37:04 GMT

Thank you so much Tamil, It worked as expected in my code

Re: Help on Calculated Dimension

Anonymous — Thu, 27 Oct 2016 09:38:42 GMT

Hi Ruben,

I used Tamil's solution itself as I want it a single row.

Thank you for your help

Regards,

Leni Balakrishnan