topic Re: How to use count in Warehouse Table in IF Condition in pipeline in Pipelines

How to use count in Warehouse Table in IF Condition in pipeline

JE_test — Thu, 02 May 2024 15:02:00 GMT

Hi!

I'm trying to create an If condition in a pipeline that should be true if a table in my DW has more than 0 rows.

I don't know how to query this table from the IF Condition.

I tried to use a lookup before to query the table (count(*) as nr_of_rows from ...) but then I don't know how to access the value nr_of_rows when writing the expression in the if statement. I tried some different variations but nothing seems to work.

The condition in my if is of the form:

@if(not(equals(activity('Lookup1').output.firstRow, '0')), true, false)

and I've tried some variations of this, but never getting the value from nr_of_rows (nr of rows in my WH-table)

Ideally I would like to not use a Lookup and only do a query in the expression of the IF condition.

Any ideas on how to solve this?

Re: How to use count in Warehouse Table in IF Condition in pipeline

Anonymous — Fri, 03 May 2024 03:39:18 GMT

Hi JE_test ,

Thanks for using Fabric Community.
As I understand you would like to perform if else operation based on value of count.

Steps to achieve it -

select count(*) as count from api.table4

@not(equals(activity('Lookup1').output.firstRow.count, '0'))

FYI: inorder to understand the loopup activity json output,
After execution of pipeline, you find the below -

Hope this is helpful. Please let me know incase of further queries.

Re: How to use count in Warehouse Table in IF Condition in pipeline

JE_test — Fri, 03 May 2024 05:43:17 GMT

Hi!

Thanks for the suggestion. My setup was close to what you describe, but now I think it is the same:

Lookup:

SELECT COUNT(*) as antal_tasks FROM [meta].[ScheduledTasks]

IF:

@not(equals(activity('Lookup1').output.firstRow.antal_tasks, '0'))

But my issue is that the IF statement still goes into the TRUE case. Not sure why. And I just double checked that the table I'm querying indeed has 0 rows.

Are we sure that we are accessing the number from the lookup and no other part of the string or something, which would make the output not exactly equal to 0?

Re: How to use count in Warehouse Table in IF Condition in pipeline

Anonymous — Fri, 03 May 2024 06:15:19 GMT

Hi JE_test ,

As part of debugging, can you please use a Set Variable and try to see the output of it?
When I tried to repro, it is working perfectly. I can go to true and false branches.

Re: How to use count in Warehouse Table in IF Condition in pipeline

JE_test — Fri, 03 May 2024 07:40:17 GMT

Thanks for the tips with using Set Variable for debugging!

Maybe the problem was that it was comparing an integer to a string and found it to be false. When I try to assign the output to a String I get this error:

The variable 'output' of type 'String' cannot be initialized or updated with value of type 'Integer'. The variable 'output' only supports values of types 'String'.

Re: How to use count in Warehouse Table in IF Condition in pipeline

JE_test — Fri, 03 May 2024 07:44:18 GMT

Yes that was it. I removed the quotes from the 0 and not it works as expected:

@not(equals(activity('Lookup1').output.firstRow.antal_tasks, 0))

instead of

@not(equals(activity('Lookup1').output.firstRow.antal_tasks, '0'))

Thanks!

Re: How to use count in Warehouse Table in IF Condition in pipeline

Anonymous — Fri, 03 May 2024 07:45:45 GMT

Hi JE_test ,

It was great to know that you were able to get to a resolution . We expect you to keep using this forum and also motivate others to do that same . You can always help other community members by answering to their queries