TM1 Forum

Posted: **Wed Apr 13, 2011 2:40 am**

Hi,
Am Using SQL to Pull data and the out put is single column and i name that varibale as 'vPlanNameSales' .Its a String and defined as 'Other'
My Aim :
Exporting Versions data to database tables.Will need to create a Subset of all versions present in Version Dim (May be 20 Versions) then Look in the database table and If it have 19 Versions then we should Load only the 20th version

Is to compare 'vPlanNameSales' with 'SUniqueVersionName' and if does not matches then Asciioutput should execute else skip:
Problem is When am using vPlanNameSales in a while loop its always pulling single record from the SQL Output.
In the IF Condition (SUniqueVersionName @<> vPlanNameSales) vPlanNameSales is always geting single value.
Here SUniqueVersionName is incrementing but vPlanNameSales is not incrementing.
When i place my AsciiOupt code Before WHile loop am able to get all vPlanNameSales values , but not when place in a While loop..
Can any one help me out please::::
Thank you

Code: Select all

[b]DATA:TAB[/b]

subsetsize = SubsetGetSize(sVerDim, subSet);
 sVer=1;
  While(sVer <= subsetsize);
  ###Test is the Subset having 30 Versions#########
  sSubsetName = SubsetGetElementName('version','Test', sVer);
  
    sVerName =  AttrS( 'Version', sSubsetName  , 'Attribute Text' );
    SUniqueVersionName = sVerName | 'D';

 IF(SUniqueVersionName @=  vPlanNameSales);
     ###Here My Other Execute Process will come########
  ASCIIOutput('C:\Documents and Settings\user\Desktop\Evening1.txt', vPlanNameSales );
     Endif;
sVer = sVer+1;
End;

Posted: **Wed Apr 13, 2011 3:22 am**

Hi:

I'm a little confused here. You say:

My Aim :
Is to compare 'vPlanNameSales' with 'SUniqueVersionName' and if does not matches then Asciioutput should execute else skip:
Problem is When am using vPlanNameSales in a while loop its always pulling single record from the SQL Output.

Buy your script says:

Code: Select all

IF(SUniqueVersionName @= vPlanNameSales);

So, your IF here appears to be doing the exact opposite of what you just said - if you want does not equal, use @<> instead of @=.

Also, just to clarify here: the Data tab is executed for each record in the source data, so if you think adding a WHILE loop will cause TI to loop through all of the records in your SQL query it will not. Your while loop is going to execute for each record in the query - that's just how it works.

I'm not sure what you're trying to accomplish here with this TI script. I get that you're trying to check something and run another process if a condition is met, but I don't follow why you're looping through an entire subset to see if a particular value from the query matches an attribute in the Version dimension. Could you, instead, have an Alias attribute in your Version dimension that matches this value coming from the SQL data. If so, you could use something like the DIMIX function to check and see if the value matches an item in the Version dimension without having to use this WHILE loop.

Sorry if I'm misunderstanding what you're hoping to do, but you may also want to explain the problem your trying to solve a little more clearly as it may not be obvious to others what you're hoping to do here. It looks like you may be making a simple problem more complicated than it may need to be.

Regards,
Mike

Posted: **Wed Apr 13, 2011 3:37 am**

Thanks for the reply.
Yes the If condition is @<>, Sorry for that.
Here in TM1 Cube Each forecast version is create and then uploaded in to Database. End of year we will have 12 Versions in Database as well as Cube.
Suppose the database is truncated and if i want to load again all the versions, i should manually touch my code and load it. For avoiding that My idea was
i will pull Versions from Database though ODBC (Select Distinct Version from Table) and take this as Variable vPlanName then compare with each element in the version DIM . If the Name matches then we will not load to database, But if the Version element is present in Subset but not in Database then we will load only this missing version.
Then Put this process in chores so that even if we lost the table in database ,our process will load all the 12 versions at a time and Missing versions from there.

Sorry If i Mislead you...

TM1 Forum

Unable to Increment the Variable in Data Tab

Unable to Increment the Variable in Data Tab

Re: Unable to Increment the Variable in Data Tab

Re: Unable to Increment the Variable in Data Tab