topic Re: JOIN multiple QVD's in QlikView

JOIN multiple QVD's

Tue, 15 Dec 2015 14:18:08 GMT

I have daily data on phone, email, and conference data in three separate QVD's. They have two common fields, USER and DATE.

From what I read, I could create one table joining all three with:

TotTab:

LOAD Date,

[CONF.Start Time],

USER,

CONF.Duration,

CONF.Notes,

CONF.StartTime

FROM

(qvd);

LEFT JOIN (TotTab)

LOAD USER,

[Email In],

[Email Out],

[Total Email],

Date

FROM

(qvd);

LEFT JOIN (TotTab)

LOAD USER,

CallLength,

hourstamp,

CTS,

[Call Time],

[Caller ID],

Destination,

Talking,

Date

FROM

(qvd);

This loads without an error message; however, there is only data from the Conf and Mail tables. No data at all from the Phone table.

What am I doing incorrectly?

Re: JOIN multiple QVD's

rwunderlich — Tue, 15 Dec 2015 14:31:56 GMT

Best guess is that either your Date or USER field values are different in the Phone table. For example, perhaps the Date field in Phone is actually a timestamp?

-Rob

Re: JOIN multiple QVD's

puttemans — Tue, 15 Dec 2015 14:33:25 GMT

Could you try to join only on one variable (User&'|'&Date) instead of 2 user and Date separately? Therefore, you rename USER and Date in each table so they won't link, and you add a new variable USER&'|'&Date as Link.

Regards,

Johan

Re: JOIN multiple QVD's

Tue, 15 Dec 2015 14:34:25 GMT

What would I do in that case? Change the name of the field in the Phone table? USER is definitely the same in all three.

Re: JOIN multiple QVD's

puttemans — Tue, 15 Dec 2015 14:34:46 GMT

Indeed, as Rob mentions, if the content of the variable is different, it will never work either.

Re: JOIN multiple QVD's

Tue, 15 Dec 2015 14:35:53 GMT

Hate to mark myself as correct, but that did it. Thanks Rob and Johan!

Re: JOIN multiple QVD's

JonnyPoole — Tue, 15 Dec 2015 14:40:08 GMT

There may be an issue joining on Date (or USER) from the phone table. Consider aligning the data types by wrapping with Date() function : Date(Date) as Date ... and loading each individually to check the date values are being intepreted the way you expect so that they will join accordingly.

If the tables are at the same granularity , then a table concatenation with an extra field called 'TYPE' could offer some interesting possibilities. One of the following 3 fields could each be added each of the loads to give the user a way to SLICE. It may also help with some set analysis.

'Email' as TYPE

'Confereance as TYPE,

'Phone' as TYPE,