Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Problem with JOIN or Crosstable

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 TYPEUS$
Handling-1
Tyre-1
Other-1
Handling

-1

Thank you!

1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

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

View solution in original post

10 Replies
Highlighted
Partner
Partner

Hi,

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

Mark

Highlighted
Creator II
Creator II

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

Thanks

Highlighted

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

Highlighted
Creator III
Creator III

Hi

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

Highlighted
Creator II
Creator II

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!

Highlighted
Creator II
Creator II

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

Highlighted
Creator II
Creator II

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.

Highlighted
Creator II
Creator II

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

Highlighted

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