Loading a table into different attribute names

[eddoria]

Hi all

I have a table on an AS400 which holds our customer proprietor names. There can be up to 10 proprietor fields. We call the attributes Proprietor1; Proprietor2 etc.

I have around 10 different process which work individually but thought it would be better to have one and use TI to determine the number of the proprietor and load it against the relevant attribute. At the moment the SQL has the individual line types. For the single process I deleted the line type criteria to bring in all records. It works for a few records but then stops and I am not sure why. The log file only shows about 70 records where there should be over a 10,000. Any assistance on why it does not work for all records would be greatly appreciate.

I have checked the source data and there is no difference between the last time that TM1 loads and the next line in the table.

The code below is my amended code on the data tab.

Code: Select all

#Trim Data
vCustomer = Trim(vAN8);
vlineNumber = Trim(vLineNumber);
vProprietor = Trim(vProprietor);

vVal=vProprietor;

#cAttribute='Name & Number';vVal=vDescription;ATTRPUTS(vVal,cDimName,vitem,cAttribute);

#Post Attributes
If(DIMIX('Customers',vCustomer)>0);
     If(vLineNumber@='0');
          cProprietor='Proprietor1';
          ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
    ElseIf(vLineNumber@='1');
          cProprietor='Proprietor2';
          ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
    ElseIf(vLineNumber@='2');
          cProprietor='Proprietor3';
          ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
    ElseIf(vLineNumber@='3');
          cProprietor='Proprietor4';
          ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
    ElseIf(vLineNumber@='4');
          cProprietor='Proprietor5';
          ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
    ElseIf(vLineNumber@='5');
          cProprietor='Proprietor6';
          ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
    ElseIf(vLineNumber@='6');
          cProprietor='Proprietor7';
          ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
    ElseIf(vLineNumber@='7');
          cProprietor='Proprietor8';
          ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
    ElseIf(vLineNumber@='8');
          cProprietor='Proprietor9';
          ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
    ElseIf(vLineNumber@='9');
          cProprietor='Proprietor10';
          ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
    ElseIf(vLineNumber@='10');
          cProprietor='Proprietor11';
           ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
    ElseIf(vLineNumber@='11');
          cProprietor='Proprietor12';
           ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
    EndIf;
EndIf;

[Edward Stuart]

What is the error message on the logfile?

[eddoria]

There is no error log. I can see a file when I save data all and see that it loaded only part of the table.

[eddoria]

even in the log file there is no error.

I have just noticed that for the last record it loads it does not load all the proprietors.

There are three proprietors - line 0, 4 and 5. So each of those should be loaded against Proprietor1; Proprietor5 and Proprietor6. But only line zero is loaded. Then the process stops. There is no quit process so I am at a loss to understand why it stops loading the table data. There are still quite a lot more records to run through.

Regards

Vera Hawkins

[u970700]

even in the log file there is no error.

I have just noticed that for the last record it loads it does not load all the proprietors.

There are three proprietors - line 0, 4 and 5. So each of those should be loaded against Proprietor1; Proprietor5 and Proprietor6. But only line zero is loaded. Then the process stops. There is no quit process so I am at a loss to understand why it stops loading the table data. There are still quite a lot more records to run through.

Regards

Vera Hawkins

Can you take the portion DIMIX If statement out and make it as stand alone condition? i.e.

Code: Select all

If(DIMIX('Customers',vCustomer)=0);
ITEMSKIP;
EndIf;

For the second part, I think the problem is the TI process should be using multiple "If" statements instead of "ElseIf".

Your sample record above highlights the problem with the logic. i.e. Since the sample record "line 0" is satisfied in the first If statement, the process would execute that portion of the If statement, bypass the rest of the code and proceed to the next record.

Cheers.

Ray

[eddoria]

Thanks for the responses.

The first if is using dim index. So most of the records have an index greater than zero so I did not think that would be an issue. This seems to be general code we use to stop trying to load customers not in the dimension.

The interesting thing is that for all the 10 separate processes we have they work each time with similar code (except for the elseif).

Code used for each single processes.

Data Query:
select
CAST (WWAN8 AS CHAR(6)) as vAN8,
WWSLNM as vProprietor,
CAST (WWIDLN AS CHAR(1)) as vLineNumber
from F0111
where WWTYC = 'D' and WWIDLN = '0'

Data Tab
#Trim Data
vCustomer = Trim(vAN8);
vlineNumber = Trim(vLineNumber);
vProprietor = Trim(vProprietor);

#Post Attributes
If(DIMIX('Customers',vCustomer)>0);
vVal=vProprietor;ATTRPUTS(vVal,cDimName,vCustomer,'Proprietor1');
Endif;

What I did for the single process is to delete the "and WWIDLN = '0'" to return all line numbers. I did write a piece of code as above for each line number. However it still stops at the same spot. I tried again with only three line numbers and it stops at the same record. This is the code I used:

#Trim Data
vCustomer = Trim(vAN8);
vlineNumber = Trim(vLineNumber);
vProprietor = Trim(vProprietor);

#Post Attributes
If(DIMIX('Customers',vCustomer)>0);
vVal=vProprietor;
If(vLineNumber@='0');
cProprietor='Proprietor1';
ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
EndIf;
EndIf;

If(DIMIX('Customers',vCustomer)>0);
vVal=vProprietor;
If(vLineNumber@='1');
cProprietor='Proprietor2';
ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
EndIf;
EndIf;

If(DIMIX('Customers',vCustomer)>0);
vVal=vProprietor;
If(vLineNumber@='2');
cProprietor='Proprietor3';
ATTRPUTS(vVal,cDimName,vCustomer,cProprietor);
EndIf;
EndIf;

[u970700]

I did a quick test with a sample set of data, and noticed a couple of things in the code.

Code: Select all

If(DIMIX('Customers',vCustomer)>0);

It has hardcoded "Customers" as the dimension, is that the correct dimension name? I noticed this because during my test I named my dimension as "zzCustomer" and the TI completed without errors, with attributes not being populated (which is what you were getting).

If not, then the code should really be updated to be consistent:

Code: Select all

If(DIMIX( cDimName,vCustomer)>0);

The DIMIX function sure just checks if a element exists, but doesn't need to be in the code multiple times to insert the attributes. Hence the check once, skip the record if element doesn't exist from my previous reply.

Also I don't see the value of setting vVal=vProprietor, seems a bit redundant.

[declanr]

Have you actually checked the dimix of the customer variable you are pulling from your query?
It is very possible it is coming down in a slightly different format than what you expect - add in an asciioutput as below to check.

Code: Select all

#Trim Data
vCustomer = Trim(vAN8);
vlineNumber = Trim(vLineNumber);
vProprietor = Trim(vProprietor);

vVal=vProprietor;

#cAttribute='Name & Number';vVal=vDescription;ATTRPUTS(vVal,cDimName,vitem,cAttribute);

sCustomerDimix = NumberToString ( Dimix ( 'Customers', vCustomer ) );
AsciiOutput ( <MyFile.txt>, vAN8, vCustomer, sCustomerDimix );

#Post Attributes
...

Edit - I would in fact Ascii out the whole of your data source to double check that what TM1 is pulling in as the source is what you expect to see - of course running the query in Management Studio etc should be fine but that doesn't cover the Trim conversions you are doing in TM1 after the fact (speaking of which - you could just do that in your query.)

[eddoria]

Thanks for the response. I will add the statement and see what the datasource is doing. But I would have thought that there would be an issue using my individual processes not just the combined one.

Thanks also for the tip about trim.

Regards
Vera

[eddoria]

I did the file and it shows me what has been happening. it stops at the same record. It is very strange. The table certainly has more than those 70 odd records. In Access with the same criteria there are hundreds of records. Also if I put in the next customer in the sql it shows it. It is only using the code that it stops.