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

[JE_test]

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?

[JE_test]

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?

[v-gchenna-msft]

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.

[JE_test]

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'.

[JE_test · Accepted solution]

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!

[v-gchenna-msft]

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

[v-gchenna-msft]

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.