Problem with JOIN or Crosstable

[Anonymous]

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!

[Mark_Little]

Hi,

Is it possible to get some more information. I.e. some sample data, your script or little QVW?

Mark

[Anonymous]

In addition to the question, it is not all values that is showing '-1'. There are some showing it's real value.

Thanks

[sunny_talwar]

Would you be able to share a sample to see what exactly is going on?

[vincent_ardiet]

Hi

Could you post a sample of your code and eventually a load inline with few lines of data producing this strange effect?

[Anonymous]

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!

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[sunny_talwar]

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],