topic Re: Join not working correctly !! in QlikView

Join not working correctly !!

nikhilgarg — Mon, 08 Dec 2014 06:54:28 GMT

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

Re: Join not working correctly !!

PradeepReddy — Mon, 08 Dec 2014 07:56:40 GMT

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.

Re: Join not working correctly !!

nikhilgarg — Mon, 08 Dec 2014 08:12:50 GMT

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>

Re: Join not working correctly !!

sujeetsingh — Mon, 08 Dec 2014 08:39:28 GMT

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) ;

Re: Join not working correctly !!

nikhilgarg — Mon, 08 Dec 2014 09:13:24 GMT

Hey,

IT is still giving same result !!

What to do ??

Re: Join not working correctly !!

PradeepReddy — Mon, 08 Dec 2014 10:02:40 GMT

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.

Re: Join not working correctly !!

nikhilgarg — Mon, 08 Dec 2014 10:09:56 GMT

HEy,

I think i have put up old file,

New File is attached

Re: Join not working correctly !!

michael_anthony — Mon, 08 Dec 2014 10:33:10 GMT

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.

Re: Join not working correctly !!

nikhilgarg — Mon, 08 Dec 2014 11:12:03 GMT

Hey,

How to use floor in date??

Thanks

Re: Join not working correctly !!

its_anandrjs — Mon, 08 Dec 2014 11:15:47 GMT

You can write

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

Regards

Anand