topic Re: Need a help on the below in QlikView

Need a help on the below

jagdishbr — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm new to qlikview and need help on the below challenge is it possible to do this in LOAD Statement.

Source data in Excel

from the above table need to split Ename and search the department they belong to from the below table. if EName appears in Trade department we need to ignore the rest of the search and update the dept. in the above table as "Trade". if not, should check for an employee if he falls under the Analytics department if not sales.

S 1. If multiple employees belongs to Trade department we still update as Trade

S 2. If an employee belongs to Trade as well Analytics then still we update the records as Trade

S 3. If an employee appears as Analytics and sales we update the record as Analytics

Reference data in Excel

Priority list

department "Trade" supersedes priority 2 (Analytics) and (3 Sales)

Re: Need a help on the below

sunny_talwar — Wed, 20 Dec 2017 12:37:41 GMT

Would you be able to provide the expected output for the data you provided once all the data is loaded

Re: Need a help on the below

jagdishbr — Wed, 20 Dec 2017 12:44:17 GMT

Sunny, it need to be update on the source data as below.

Re: Need a help on the below

sunny_talwar — Wed, 20 Dec 2017 12:47:22 GMT

How did you get the 4th row in your table?

Re: Need a help on the below

jagdishbr — Wed, 20 Dec 2017 12:52:59 GMT

Just as an example I have added

Re: Need a help on the below

sunny_talwar — Wed, 20 Dec 2017 13:24:48 GMT

Try like this

Table:

LOAD * INLINE [

Ename, Dept

Michle, Analytics

Mini, Trade

Alex, Sale

Gibs, Analytics

Iva, Sale

Roshan, Sale

Andy, Sale

Sidebottom, Trade

Paul, Sale

];

Left Join (Table)

LOAD * INLINE [

Dept, Priority

Trade, 1

Analytics, 2

Sale, 3

];

MappingTable:

Mapping

LOAD Ename,

Priority

Resident Table;

Fact:

LOAD *,

If(SubStringCount(MapSubString('MappingTable', EnameList), '1'), 'Trade',

If(SubStringCount(MapSubString('MappingTable', EnameList), '2'), 'Analytics', 'Sale')) as Dept;

LOAD * INLINE [

Date, EnameList

20/12/2017, "Michle, Mini, Alex"

12/12/2017, "Gibs, Iva, Roshan"

13/12/2017, "Andy, Paul, Sidebottom"

20/12/2017, "Roshan, Andy, Alex"

];

Re: Need a help on the below

jagdishbr — Wed, 20 Dec 2017 14:59:41 GMT

Hi Sunny,

Thanks for your effort and the solution.

Observation:

output should not increase the number of rows and if you observe 2nd row of the qv output "michle" is not part of EnameList for second row

Output:

Input and output no of records should be the same

Input fields are date and Ename and the Output should be date, Ename and dept

Re: Need a help on the below

jagdishbr — Wed, 20 Dec 2017 15:04:17 GMT

Sunny,

Apologize for the above post. it's my bad, I have added all the fields. Please ignore.

It worked as expected. Thanks a lot for your help.

Re: Need a help on the below

sunny_talwar — Wed, 20 Dec 2017 15:05:13 GMT

I guess you are seeing this because of these two tables

Do you not want them in the script after everything is done? If not, then you can drop it, because if you look at your Fact table, we only have 4 rows

New Script if the above two tables are not needed anymore

Table:

LOAD * INLINE [

Ename, Dept

Michle, Analytics

Mini, Trade

Alex, Sale

Gibs, Analytics

Iva, Sale

Roshan, Sale

Andy, Sale

Sidebottom, Trade

Paul, Sale

];

Left Join (Table)

LOAD * INLINE [

Dept, Priority

Trade, 1

Analytics, 2

Sale, 3

];

MappingTable:

Mapping

LOAD Ename,

Priority

Resident Table;

Fact:

LOAD *,

If(SubStringCount(MapSubString('MappingTable', EnameList), '1'), 'Trade',

If(SubStringCount(MapSubString('MappingTable', EnameList), '2'), 'Analytics', 'Sale')) as Dept;

LOAD * INLINE [

Date, EnameList

20/12/2017, "Michle, Mini, Alex"

12/12/2017, "Gibs, Iva, Roshan"

13/12/2017, "Andy, Paul, Sidebottom"

20/12/2017, "Roshan, Andy, Alex"

];

DROP Table Table;

Re: Need a help on the below

sunny_talwar — Wed, 20 Dec 2017 15:06:01 GMT

Hahahaha, no problem... but how is this correct response?