topic Re: Row Count Macros not working in Straight table in QlikView

Row Count Macros not working in Straight table

RaoPS — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have created a macro which will count the number of rows in the straight table.
But when the straight table is having "calculation condition" feature set, this macro won't give the proper result.
When I remove the "calculation condition" feature, macro is working fine.

Straight table without condition : Macros used in Caption giving the right count : 50336

Straight Table condition:

After selecting rows greater than 50k with the condition : Here Macro still works fine at caption showing 50336

After performing the above step, now If I select something which has less than 50k records macros gets updated to 1

This occurs only when the condition is applied.
Can anyone help me with this?

Re: Row Count Macros not working in Straight table

sunny_talwar — Tue, 04 Aug 2020 16:13:53 GMT

Why do you need a macro to do this calculation?

Re: Row Count Macros not working in Straight table

RaoPS — Wed, 05 Aug 2020 12:32:20 GMT

Hi Sunny,

Since the column selection is dynamic, I need to calculate row count every time any column is selected or deselected and multiple tables are involved here hence not able to consider the primary key column to the count.

Re: Row Count Macros not working in Straight table

sunny_talwar — Wed, 05 Aug 2020 15:16:41 GMT

I have a feeling that this can be done without a macro, but I won't really know unless I see it

Re: Row Count Macros not working in Straight table

marcus_sommer — Thu, 06 Aug 2020 11:46:11 GMT

I agree with Sunny that there might be other solutions than a macro to get this count, maybe with an approach like the following:

count(distinct $(='[' & concat(DimensionsListbox, ']&[') & ']'))

It exact logic and syntax depends on the way how the dynamic approach is implemented but because of the fact that the user-selection of those selections/variables is used to control the visibility and calculations in the object - they could be also used to calculate the number of rows in it.

- Marcus

Re: Row Count Macros not working in Straight table

RaoPS — Sat, 08 Aug 2020 11:16:55 GMT

Please find below one of the scenarios for which row count is calculated.

And my requirement involves multiple columns and tables.
There are many such scenarios involved with multiple tables and to calculate row count on any primary key is complicated.

Re: Row Count Macros not working in Straight table

Kushal_Chawda — Sat, 08 Aug 2020 13:54:13 GMT

I don't think you need macro here. But calculation will really depend on your dimension and expression. In your above example what is expression?

Re: Row Count Macros not working in Straight table

sunny_talwar — Mon, 10 Aug 2020 11:06:07 GMT

Do you have multiple tables or do you have one table where you dynamically allow your users to select dimension? If it is later, then do you allow this using a list box to pick dimensions? All we need to understand is your setup... because essentially you can create a Sum(Aggr(Count()...)) function based on selection of your list box to figure out which dimensions do you have and use them in Aggr() function.

Re: Row Count Macros not working in Straight table

Brett_Bleess — Tue, 18 Aug 2020 18:51:38 GMT

So I want to say I agree with the other posters on this one, macros should be the last resort when doing things in QlikView, and it seems they have some other ways of doing things, so my advice would be to follow up on those and see if one of them will work or provide the additional info they requested to try to help you further. If you really want to stick with the macro, the issue here is going to be one of timing, and you are going to need to use the WaitForIdle API call at the start of the macro most likely in order to ensure that timing is such that the calculation condition fully runs prior to the macro trying to do things, that would be the only potential solution for the macro issue I believe.

Regards,
Brett