Check Dimension Totals Before Loading

[Lena4]

Hi, 

I'm working on a load script for a new app and want to validate that I don't have duplicate Item numbers before loading. If there are duplicates, I want to stop loading.  My code is not working, but I'm not sure why. If I look at the variables in a sheet, there is a value, but not when I try to print them using Trace, even when it's outside of the IF statement. . 

Values:

LOAD

   ItemNumber

   Date,

    Price

FROM [*QVD File*]

WHERE

year(Date) >= year(AddYears(Today(),-1));

Right keep(Values)

LOAD

    NAME as ItemNumber,

    Target

Description

Color

Size

Status

FROM [*Excel File*]

Let vDistinctNumberCount = Count(distinct TAGNAME);
Let vNumberCount = Count(TAGNAME);

If $(vNumberCount) <> $(vDistinctNumberCount) then

    TRACE "*******Error Total ItemNumber count > Distinct ItemNumber count";

End If;

[rubenmarin]

Hi, Count works on charts, in script it only works using it on a load sentence of a table, you can crete a temporary table to do the counts and use peek to retieve the values, like:

Values:
LOAD...
Right Keep (Values)
LOAD...
FROM [*Excel File*]:

tmpCount:
LOAD 
  Count(ItemNumber) as NumberCount,
  Count(Distinct ItemNumber) as DistinctNumberCount
Resident Values;

LET vNumberCount = Peek('NumberCount',0);
LET vDistinctNumberCount = Peek('DistinctNumberCount',0);

DROP Table tmpCount;

If $(vNumberCount)...

But if the final table should end with a row for each different ItemNumber you can just do the load of the data and then do the comparison as:

IF NoOfRows('Values')<>FieldValueCount('ItemNumber') THEN
  TRACE ...
END IF

[Lena4]

Thanks for taking a look at this. I tried both options and haven't been able to get the trace to work, although it seems that it should.

I found a resource that suggested using an error message in brackets to stop the load. So, the code below works but I'm getting an error symbol before I even start running. 

IF NoOfRows('Values') <> FieldValueCount('ItemNumber')THEN
[Error: ***There are duplicate values in the table****];
End If

Is there a better way to accomplish what I'm trying to do?

[rubenmarin]

Hi, that error symbols is shown when there is a syntax error, and the solution applied is to create a syntax error, so it's normal it detects that as an errror, you should be able to execute the script anyway, but instead of finishing the reload it will thrown an error when it enters the 'IF'