topic Re: Problem with JOIN or Crosstable in QlikView

Problem with JOIN or Crosstable

Anonymous — Mon, 16 Nov 2015 13:38:16 GMT

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!

Re: Problem with JOIN or Crosstable

Mark_Little — Mon, 16 Nov 2015 13:43:58 GMT

Hi,

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

Mark

Re: Problem with JOIN or Crosstable

Anonymous — Mon, 16 Nov 2015 13:44:29 GMT

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

Thanks

Re: Problem with JOIN or Crosstable

sunny_talwar — Mon, 16 Nov 2015 13:45:12 GMT

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

Re: Problem with JOIN or Crosstable

vincent_ardiet — Mon, 16 Nov 2015 13:45:50 GMT

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

Re: Problem with JOIN or Crosstable

Anonymous — Mon, 16 Nov 2015 13:57:32 GMT

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!

Re: Problem with JOIN or Crosstable

Anonymous — Mon, 16 Nov 2015 15:52:58 GMT

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

Re: Problem with JOIN or Crosstable

Anonymous — Mon, 16 Nov 2015 16:02:49 GMT

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.

Re: Problem with JOIN or Crosstable

Anonymous — Mon, 16 Nov 2015 16:56:53 GMT

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

Re: Problem with JOIN or Crosstable

sunny_talwar — Mon, 16 Nov 2015 17:17:49 GMT

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

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

Re: Problem with JOIN or Crosstable

Anonymous — Mon, 16 Nov 2015 17:44:02 GMT

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