Modifying tables in script

[#1]

Hi all,

First of all, I am relatively new to QlikView and trying to learn and improve. I'm trying to load data which contains 1 table as following:

Abbr.     Person

----------------------------

LC        Luc Cornet

PK        Patrick Krishoan

...          ....

As you can see, this table contains the abbreviations and the full name.

I also have another table which is called "Employee" as follows:

EmpID   FirstName   LastName

-----------------------------------------------

1            Luc               Cornet

2            Patrick          Krishoan

...

The context is as following. People who are defined in the first table are sales-persons. They are also defined in the other table which is the Employee table. I need to be able to filter out only sales persons and I need to have an easy way to handle sales persons only. At the same time, I'd like to go for a solid solution. I've been thinking about having a new field in the Employee table which is "salesAbbreviation". When an employee has an abbreviation, we know that he is a sales person. This solution seems to be good enough. However, if every employee starts to get an abbreviation, then this is not enough anymore. In that case, I guess that we'd need another field "isSalesPerson" which is YES/NO. By doing set-analysis, I could filter out only sales persons.

Does this make sense? How should this be done, what's the best approach ? Any small code snippets are welcome as well.

Thanks for your help.

KRs

[sunny_talwar]

You can do something like this:

Table1:

LOAD Abbr.

          Person

FROM Source1;

Table2:

LOAD EmpID,

          FirstName,

          LastName,

          FirstName & ' ' & LastName as Person

FROM Source2;

[#3]

Whoops that was easy. Thanks !

However, now that I am trying to get only the persons who have an abbreviation, I figured out that I cannot do set analysis without an aggretation function. What's the best way to do that ?

[sunny_talwar]

Try this:

Concat({<Abbr = {'*'}>} Person, ', ')

[#5]

That doesn't seem to work. I've added a listbox whereby I've added this expression to the field. But the listbox doesn't show any information.

Just to be sure that you understand me correctly, this is the datamodel right now :

I've managed to populate the listbox with all the employees but that also doesn't give the correct results. This is my loading script line in the employees table:

EmpID & '|' & [First Name] & ' ' & [Last Name] as Person,

This is the loading of the SalesInitials:

SalesInitials:

LOAD SP as Abbreviation,

     SalesPerson as Person

FROM ....

And this is configured in my listbox :

Which results in the following:

Which is obviously not correct. As you can see, "Frank Roll" is defined in that listbox twice, once without initials and once with intials. Furtermore, Tom Lindwall also has an initial but is not shown here.

What could be the reason? 

So I have 2 questions right now

1) Why do I see the employees twice, and why does Tom Lindwall not show "TL" as abbreviation?

2) Still the initial question, I only want to show the people who have initials defined.

Thank you very much for your time.

[sunny_talwar · Accepted solution]

2)For list box object, try this expression:

Aggr(Only({1<Abbr = {'*'}>} Person), Person)

1) Try this without the EmpID

EmpID & '|' & [First Name] & ' ' & [Last Name] as Person,