topic Re: Check Dimension Totals Before Loading in App Development

Check Dimension Totals Before Loading

Lena4 — Fri, 07 Jun 2024 21:43:43 GMT

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;

Re: Check Dimension Totals Before Loading

rubenmarin — Sat, 08 Jun 2024 05:56:41 GMT

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

Re: Check Dimension Totals Before Loading

Lena4 — Mon, 10 Jun 2024 16:39:13 GMT

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?

Re: Check Dimension Totals Before Loading

rubenmarin — Tue, 11 Jun 2024 06:58:48 GMT

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'