Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Accepted Solutions
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.
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
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.
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;
