topic Join and filling up empty Data in QlikView

Join and filling up empty Data

sebastianfaust — Fri, 31 Jan 2014 10:18:49 GMT

Hi all,

i have two tables, Contracts and Meetings.

In meetings every meeting a salesman had is saved ,

in contracts the signed contracts are saved.

id like to create 3 kpis.

1. Amount of Contracts in a year

2. Amount of Meetings in a year

3. how many meetings were needed for one contract

this is kinda easy.

but donald has a meeting which is not relevant for the kpi,

so if i select only the relevant "given" contacts, it will drop all contracts out of my current selection.

cause in contracts the "relevant" column is filled with NULL.

I have in mind that you can fill up the NULL with something like a wildcard?

hope someone understand what iam trying to do ^^

also i added the qvw as an attachment.

thx in advance.

Re: Join and filling up empty Data

hic — Fri, 31 Jan 2014 10:26:47 GMT

You will need to do this when you run the script, e.g. by using

If( IsNull(Relevance), 'NotGiven', Relevance )

or better

If( Len(Trim(Relevance))=0, 'NotGiven', Relevance )

HIC

Re: Join and filling up empty Data

sebastianfaust — Fri, 31 Jan 2014 10:34:48 GMT

hi henric,

thanks for your quick reply.

this would resolve the problem in the first state, yes.

maybe i should have added more detail.

i have more than one state on relevance.

in fact i have a catalogue behind that.

so the state can be something between 1 and 9.

edith:

Meeting:

LOAD * INLINE [

ID_Meeting, Person, Date, Year, Relevance

0000, Gustav, 01.01.2013, 2013, 1

0001, Gustav, 01.01.2013, 2013, 2

0002, Donald, 01.01.2013, 2013, 1

0003, Donald, 01.01.2014, 2013, 8

0004, Donald, 01.01.2014, 2014, 3

];

Re: Join and filling up empty Data

senpradip007 — Fri, 31 Jan 2014 10:38:52 GMT

try this

Meeting:

LOAD * INLINE [

ID_Meeting, Person, Date, Year, Relevance

0000, Gustav, 01.01.2013, 2013, given

0001, Gustav, 01.01.2013, 2013, given

0002, Donald, 01.01.2013, 2013, given

0003, Donald, 01.01.2014, 2013, notGiven

0004, Donald, 01.01.2014, 2014, given

];

join

LOAD * INLINE [

ID_Contract, Person, Date, Year

0000, Gustav, 02.01.2013, 2013

0001, Donald, 02.01.2013, 2013

0002, Donald, 02.01.2014, 2014

];

NewTab:

NoConcatenate

LOAD

ID_Contract,

ID_Meeting,

Person,

Date,

Year,

If(len(Trim(Relevance)) = 0, 'Not Given', Relevance) As Relevance

Resident Meeting;

DROP Table Meeting;

Re: Join and filling up empty Data

sebastianfaust — Fri, 31 Jan 2014 11:51:30 GMT

@pradip

thanks, but this doesnt solve the problem as soon as you have more than one state on relevance

goal is that kpi 2 only counts all relevant meetings, which would be 4.

but still all contracts.

meanwhile i found something about generic keys, iam still not sure if this could solve my problem.

anyone worked with generic keys so far?

Re: Join and filling up empty Data

tresB — Fri, 31 Jan 2014 12:59:17 GMT

Not sure if I understood, try something like:

=Count(Date)-Count({<Relevance={'given'}>} Date)