Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to do a count of item numbers where a specific field (corporate_level_1) is missing data.
I have the following syntax :
=count({1<Item_number = P({1<{=len(corporate_level_1)=0}>} Item_number)>} Item_number )
I have no result, is there something missing in the syntax ?
Thanks
Try like:
=count({1<Item_number - ={'*'}>} Item_number ) // note the '-' symbol before '='
=count({1<Key = {'=len(Trim(corporate_level_1))=0'}>} Key )
Or
=count({1-<Item_number = {'*'}>} Key )
Replace Key by your Key field in the table or any non-nullable field
This should be done using a flag in your load script.
If(isnull(corporate_level_1),1,0) as %MissingCorporateLevel1Flag
then your expression becomes
count({1<%MissingCorporateLevel1Flag={1}>}distinct Item_number)
Hi
please try below expression
=count({1<Item_number = P({1<Item_number={"=len(corporate_level_1)=0"}>} Item_number)>} Item_number )
Use a flag in the script, for the existence of the filed.. and use this filed in the expression.
Script:
Load *,
IF(corporate_level_1=' ','N','Y') AS Corporate_level_1_Flg
From...;
Expression:
Count({<corporate_level_1_Flg={'N'}>}Item_ID)
It works now like this :
=count({1<Item_number = {'=len(Trim(corporate_level_1))=0'},CBU={4870},Active_status={"##"},Material_type={ZFPR} >} Item_number )
I also added other conditions,
BUT
although I entered 1, the result changes if I change manually the selection in the screen
Thanks for the reply