IF statement Syntax

Post Reply
HarshaPatil
Posts: 6
Joined: Tue May 23, 2023 8:23 am
OLAP Product: IBM Cognos TM1 Architect
Version: IBM Planning Aalytics 2.0
Excel Version: 15 0 4569 1504

IF statement Syntax

Post by HarshaPatil »

Hello.

I am very new to TM1 and I need some urgent assistance with an 'IF' statement in a TI Process.

I have a cube that contains a Daily data ,. Lets say the data looks like :vBudget_KPIs(200 KPis) , vBranch_Master(5000 branches) ,vDays ,vYear, vValue .

As these data is load on daily basis. Data is totally depends on source files .We have some chores which schedule at fix time. if data completes from source side before our schedules then data is loaded correctly by chore if they run after then we have zero data in Cube.

And we have dependency on that cube.

So basically i have to check if data is present then load process if data is zero then stop loading of the process..

IF(vBudget_KPI(300045) = 0 );
TerminateProcess(process_Name);
ELSE
ExecuteProcess(process_Name);
ENDIF;

Is this syntax is correct...?

Basically...

I have to check if budget kpi 300045 is zero then stop loading the process else run process. which syntax should i use to stop process i don't think terminateprocess is correct..?

Any help will be appreciated thanx!
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: IF statement Syntax

Post by Wim Gielis »

You have basically 2 (or more) approaches.

Approach 1 is to have the data source as usual but add a counter in the Prolog tab: vCounter = 1;
Then in the Data tab as the first line: vCounter = vCounter + 1:
You do your zero out in the Data tab instead of the Prolog tab:
If( vCounter = 1 );
# do your zero out
EndIf;

After that, write your CellIncrementN and CellPutS functions to load data in the cube.

Since the Data tab is only reached when there are records in the data source, you will only clear and load values in case source data exists.

If the process should also update metadata, you can use similar ideas.

The second option is to have 2 processes. Process 1 checks whether there is data (using a flag in a table or a top 1 statement) and in the Data tab you could call a second process with ExecuteProcess. If that data tab is not reached the second process (including unwinding of dimensions and/or a zero out) is not executed and no harm done.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: IF statement Syntax

Post by Wim Gielis »

This generic process could be useful:
https://www.tm1forum.com/viewtopic.php?p=72589#p72589

Test it and understand it before using in a production environment.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
HarshaPatil
Posts: 6
Joined: Tue May 23, 2023 8:23 am
OLAP Product: IBM Cognos TM1 Architect
Version: IBM Planning Aalytics 2.0
Excel Version: 15 0 4569 1504

Re: IF statement Syntax

Post by HarshaPatil »

Thanks Wim Gielis for the reply.

We have total 6 processes to load daily Data in Actual Cube.

1. We have chore schedule at 8:40 AM but if source side(ETL) finish there data in between 8:40 AM to 8:41 AM in that situation our 1st process triggered exact 8:40 so half data is gone in cube(some KPIs Value is missing). After that we have dependency on Actual Data Cube that Dashboard process is also schedule at fix time.
So basically initial data(Actual Data) is wrong so we have to delete that one & reload manually that all Manual work. we are trying to automate all process.

if we zero out & to load the Actual data again it takes approx. 1 Hr so Zero out is not possible to us as clients are also using that data.

Problem is where when we have half data in Actual Cube and another Dashboard process is triggered at 9:25 AM using Actual Data which is wrong data and it makes flag Y . so we have to tackle this situation.

In Dashboard process we have to set Flag Y if data is finished.
1. We have two versions a. Actual b. Actual_Average[for avg = data/no of days(from 1 Apr to 31 Mar) formula is used]
so for actual we tackle if data is zero then flag set Y
But For Avg data is calculated in cube so if Actual is zero then also Avg have some values so

IF(vBudget_KPI@='30045' & vValue@= '0' );
this condition is not useful for this situation

Is there any solution to avoid this Manual Work...?

Thanks in advace
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: IF statement Syntax

Post by Wim Gielis »

Did you ask the source system to provide a flag when ready ?
Then have a chore at regular intervals, checking the flag and proceed when the flag indicates the data is ready ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
HarshaPatil
Posts: 6
Joined: Tue May 23, 2023 8:23 am
OLAP Product: IBM Cognos TM1 Architect
Version: IBM Planning Aalytics 2.0
Excel Version: 15 0 4569 1504

Re: IF statement Syntax

Post by HarshaPatil »

Yes I asked source team they agreed to give us completion status as "C" in table

Should I have to check flag condition in Advanced-> Data Tab before SQL statement(as we are exporting data from cube to table).
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: IF statement Syntax

Post by Wim Gielis »

You would do it in a separate process and pass the outcome of the process to the first, major, process responsible for the export. A simple IF would then decide whether the export needs to take place, or needs to be skipped.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
HarshaPatil
Posts: 6
Joined: Tue May 23, 2023 8:23 am
OLAP Product: IBM Cognos TM1 Architect
Version: IBM Planning Aalytics 2.0
Excel Version: 15 0 4569 1504

Re: IF statement Syntax

Post by HarshaPatil »

Thanks wim for the reply. I'll try this method.
Post Reply