topic Re: Joined Table in QlikView

Joined Table

evansabres — Tue, 05 Jun 2018 13:33:01 GMT

I have two tables.

First table is

TW:

twID,

post,

postimp,

posteng

FROM [Internal Data Source]

Video:

twID,

videopost,

videoviews

FROM [Internal Data Source]

They are linked by the field twID. I want to create a field called 'Post Type' where if the number of video views = null, then the post-type is called non-video.

Would this be done in the script or within the application?

Re: Joined Table

evansabres — Tue, 05 Jun 2018 13:34:12 GMT

The video views field does not exist in the table TW, which is why I am having an issue

Re: Joined Table

fannybenand — Tue, 05 Jun 2018 13:40:03 GMT

Hi Evan,

In the video table, is there is multiple lines for a twID or only one line by twID ?

Re: Joined Table

YoussefBelloum — Tue, 05 Jun 2018 13:42:15 GMT

Hi,

you can try this:

TW:

twID,

post,

postimp,

posteng

FROM [Internal Data Source]

Video:

twID,

videopost,

videoviews,

if(len(trim(videoviews))=0,'non-video') as post_type

FROM [Internal Data Source]

Re: Joined Table

evansabres — Tue, 05 Jun 2018 13:42:32 GMT

each line has only one twID. For context these are Twitter posts. For each post there is a twID and then various data points. One table has the video views (Video) while the other table does not. Basically, I want to be able to create a filter on post-type where I can choose all fields that are null for video views

Re: Joined Table

evansabres — Tue, 05 Jun 2018 13:43:57 GMT

Hi Youssef, thank you that does make sense. However I think that I did not provide enough information in my first post. The first table TW, does not have the field videoviews, which is where I am running into the problem

Re: Joined Table

fannybenand — Tue, 05 Jun 2018 13:45:15 GMT

In this case, Youssef answer should works well !

Re: Joined Table

evansabres — Tue, 05 Jun 2018 13:55:22 GMT

I think I can see how I can make this work for my purposes. How can I expand on this to have something along the lines of, if [video views] <0, 'Non-Video'.

Re: Joined Table

YoussefBelloum — Tue, 05 Jun 2018 14:00:58 GMT

you're welcome !

Actually, I don't see a problem here, because you're able to join the tables using twID field,

after creating your field on your second, table, you will be able to filter using that field and you will see post, postimp, posteng informations related to that filter value 'null'

did you give it a try ?

Re: Joined Table

captain89 — Tue, 05 Jun 2018 14:38:40 GMT

it should be:

if(isnull(videoviews), 'non-video', 'video')

Re: Joined Table

petter — Tue, 05 Jun 2018 14:46:10 GMT

You seem to have posted the same question multiple times. This is not a good practice due to the fact that different people will try to answer the question in separate threads while not having the benefit of seeing if the question already has been dealt with. It's a waste of other peoples time.

The Community Guidelines discourage such a practice. Please read them and follow them.

Thanks

Re: Joined Table

evansabres — Tue, 05 Jun 2018 15:08:43 GMT

Thanks, clearly this was not done intentionally

Re: Joined Table

evansabres — Tue, 05 Jun 2018 15:12:46 GMT

I found this works adequately as well

Re: Joined Table

captain89 — Tue, 05 Jun 2018 15:19:33 GMT

Sorry i wrote video instead of 'video'....

Re: Joined Table

evansabres — Tue, 05 Jun 2018 15:22:02 GMT

Thanks for your help

Re: Joined Table

evansabres — Tue, 05 Jun 2018 15:25:07 GMT

Using this, I can only get 'video' to return as a value, not 'Non-Video'

Does this have anything to do with the fact that these are only linked tables as opposed to joined tables?

Re: Joined Table

captain89 — Tue, 05 Jun 2018 15:49:40 GMT

Try using mapping and applymap:

Video:

twID,

videopost,

videoviews

FROM [Internal Data Source];

map_views;

mapping load text(twID) as K1, videoviews as V1 resident Video;

drop table Video;

TW:

load

applymap('map_views', text(twID), 'non-video') as post_type,

twID,

post,

postimp,

posteng

FROM [Internal Data Source]

Re: Joined Table

evansabres — Tue, 05 Jun 2018 17:20:22 GMT

What do the K1 and V1 represent?

Re: Joined Table

captain89 — Thu, 07 Jun 2018 08:03:47 GMT

Nothing. They are only a label. But I rename the fields to distinct them from the main table.

Applymap is similar to the concept of VLOOKUP function in excel.