Join not working correctly !!

[nikhilgarg]

HEy,

In my application , join is not working corretly.

I have 2 tables. If Server_Analysis1. Event = Server_Analysis2. Event and Server_Analysis1.StartDate = Server_Analysis2.StartDate and Server_Analysis1. EndDate = Server_Analysis2. EndDate   then ,  tot hat corresponding rows , Server_Analysis2. StartTime and Server_Analysis2. EndTime (these 2 new columns get added). 

But when i put join , a new rows gets added instead of adding 2 columns.

Please help.

Thanks

[PradeepReddy]

you are doing the full outer join based on the field "EventState Type" but there is no common data b/w the 2 tables for this field. so the data will come from both the tables, if there is no match.

[nikhilgarg]

Heyy,

I didnt get you.

In serveranalysis1 and server analysis2 :

I have event state type type(serverdown) , startdate and enddate common for

some rows. Then why there is no join between them ??

On 08-Dec-2014 1:27 PM, "pradeep taniparthy" <qcwebmaster@qlikview.com>

[sujeetsingh]

Use left join ()

Event you can go for Mapping option

Try this

Server_Analysis1:

LOAD

[EventState Type] as Event,

  Date([Event Start Time], 'DD.MM.YYYY') as StartDate,

      Date([Event End Time], 'DD.MM.YYYY') as EndDate,

Time([Event Start Time] + '2:30:00', 'hh:mm:ss') as StartTime,

     Time([Event End Time]  + '2:30:00', 'hh:mm:ss') as EndTime,

     MonthName([Event Start Time]) as Month_Year,

     Interval(-(Date([Event Start Time]) - Date([Event End Time])))as Duration

//     [Event Start Time] ,

//     [Event End Time],

//     [Event Duration],

//  

//     [EventState Information],

FROM

(ooxml, embedded labels, table is [Detaied Report]);

left join Join(Server_Analysis1)

Server_Analysis2:

LOAD [EventState Type] as Event ,

     StartDate ,

     EndDate  ,

     StartTime as Downtime_StartTime ,

     EndTime as Downtime_EndTime    

FROM

(ooxml, embedded labels, table is Sheet1) ;

[nikhilgarg]

Hey,

IT is still giving same result !!

What to do ??

[PradeepReddy]

I mean to say...

In Server_Analysis1 table, the field 'Event' has the values

HOST UP (HARD),

HOST DOWN (HARD)

In Server_Analysis1 table, the field 'Event' has the values

Server_DOWN

if you join based on this field you won't get any matching recording from both tables.

[nikhilgarg]

HEy,

I think i have put up old file,

New File is attached

[michael_anthony]

Is it correct that wanting to join on Event, StartDate and EndDate as they are the common fields.

IN Server_Analysis1 does tge Event Start Time have date and time?  That maybe problem.  The date( ) function you wrap it in only changes the format, not the value.  It may still be joining on the full datetime and so no match.  Maybe try wrapping in a floor () function to remove time portion.

[nikhilgarg]

Hey,

How to use floor in date??

Thanks

[its_anandrjs]

You can write

Date( Floor ( [Transaction Date] ), 'DD.MM.YYYY') as TransacDate,

Regards

Anand