Join and filling up empty Data

[sebastianfaust]

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.

[hic]

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

[sebastianfaust]

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

];

[senpradip007]

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;

[sebastianfaust]

@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?

[tresesco]

Not sure if I understood, try something like:

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