Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am experiencing a problem using a join or crosstable. A numeric value is set to -1 automatically where it was about to show another value instead.
Does anyone had the same problem? How can I fix that? I would like to sum all these values instead of '-1' and I am getting the wrong result.
Below is the table
MTX TYPE | US$ |
Handling | -1 |
Tyre | -1 |
Other | -1 |
Handling | -1 |
Thank you!
Hi Sunny.
Just tested and it doesn't work.
The solution is below.
if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
if([TEST Type]='STD',
if(Handling='0', Handling, Handling='0')),
[Handling]) as [Handling],
Hi,
Is it possible to get some more information. I.e. some sample data, your script or little QVW?
Mark
In addition to the question, it is not all values that is showing '-1'. There are some showing it's real value.
Thanks
Would you be able to share a sample to see what exactly is going on?
Hi
Could you post a sample of your code and eventually a load inline with few lines of data producing this strange effect?
Yes, I think the problem can be in the load script.
Gonna format it in a proper way an then I post the code here.
Thank you!
Folks,
Below is the load script.
Cars:
LOAD Model__c,
[S/N],
MTX_START,
REGION,
SUBREGION
FROM
AAA
(ooxml, embedded labels);
join(Cars)
LOAD Model__c,
[S/N],
[TEST Type],
[TEST Start Date],
[TEST End Date]
FROM
BBB
(ooxml, embedded labels, table is [Enrollment]);
join (Cars)
LOAD Model__c,
TIMEFRAME,
num(Handling, '#.##0,00') as [Handling],
num(Tyre, '#.##0,00') as Tyre,
num(Other, '#.##0,00') as Other
FROM
CCC
(ooxml, embedded labels, table is [TT FINAL]);
TableA:
load
Model__c,
[S/N],
MTX_START,
REGION,
SUBREGION,
[TEST Type],
[TEST Start Date],
[TEST End Date],
//Maintenance Date Calculation
Date(MonthName(TIMEFRAME + MTX_START), 'MM/YYYY') as [MTX Date],
Handling,
Tyre,
Other,
// TEST Vingent calculation
if(Date(MonthName(TIMEFRAME + MTX_START), 'MM/YYYY')>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and Date(MonthName(TIMEFRAME + MTX_START), 'MM/YYYY')<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
if([TEST Type]='STD', num(Tyre + Other + Handling,'#.##0,00'),0),0) as [TEST STD]
Resident Cars;
drop table Cars;
//removing Duplicated Values
Conditional:
load
Model__c,
[S/N],
MTX_START,
REGION,
SUBREGION,
[TEST Type],
[TEST Start Date],
[TEST End Date],
TIMEFRAME,
SCHEDULED_MTX,
MTX_MONTH as [MTX Type],
//Separate calculations for chart
[Handling] as [ Handling Chart],
[Tyre] as [Tyre Chart],
[Other] as [Other Chart],
//Conditional for removing duplicated values
if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
if([TEST Type]='STD', [Handling]=0),
[Handling]) as [Handling],
if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
if([TEST Type]='STD', [Tyre]=0),
[Tyre]) as [Tyre],
if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
if([TEST Type]='STD', [Other]=0),
[Other]) as [Other]
resident TableA;
drop Table TableA;
FinalTable:
CrossTable ([MTX Type], [US$],11)
load *
Resident Conditional;
DROP Table Conditional;
Thanks
Apparently the problem is in table 'Conditional'. If I remove it and all tables below in load script I get no values in User Interface.
Almost finding the solution.
The error is in this Step:
if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
if([TEST Type]='STD', [Handling]=0),
[Handling]) as [Handling],
Where it sets -1 to all zeros values. I am looking for non zeros values in order to see if it is replacing any value I want.
I will keep this post update as I am debugging the code.
Thanks
May be this:
if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
if([TEST Type]='STD', 0),
[Handling]) as [Handling],
or
if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
if([TEST Type]='STD', 0, [Handling]),
[Handling]) as [Handling],