Load data based on conditions

[jason_nicholas]

I apologize if this is already answered, but I could not find a fully-matched question in the existing threads.

I am importing scheduling data from an Excel sheet (let's call it "Import"), which contains a "Room Code" column listing the room names. I only want to use data from bookings which take place in a sub-set of these rooms, and completely ignore the rest. I created and imported a list of the rooms I want to focus on, and labeled it "Room". I also tried listing this also as "Room Code", which associated the table with the rest, but then included all room names and not just the selection I am looking for.

Note- there are dozens of acceptable room names under "Room Code", so a script individually referencing each desired option would not be ideal. This is why I created the separate "Room" list.

How could I look only at data from "Import" where the result in "Room Code" exists on "Room"? I have a vague idea of the context within the load script, but I don't fully understand the syntax in which I would write the code.

A secondary, but related question:

In another column, I have a list of booking names, called "Event Name". Within this list, there are certain items I would like to remove based on specific key words. For instance, I don't want to use the events where "rehearsal" appears in the title (I have about 6-10 key words which would indicate removal). One caveat to this issue: one of my key words is "Set", which can appear in the context of "Set Up", or "setup", or "Set Day". However, I don't want to remove cases where the word is "asset", or contains the name "Seth".

Is there a way to manage this data, or would I be better off manually pulling this data from the initial Excel export before importing it into QlikView?

I would appreciate any help you could provide. Thank you.

[sunny_talwar]

for your first part, you can try this:

LOAD Room

FROM ....;

MainTable:

LOAD ....

FROM Excel

Where Exists(Room, [Room Code]);

[sunny_talwar]

Second part seems to be multiple things, but breaking it into two parts

1) Exclude rehearsal in title

Where not title LIKE '*rehearsal*';

or

Where not WildMatch(title, '*rehearsal*');

2) Remove Set, but leave asset and Seth

Where not match(title, 'Set Up', 'setup', 'Set Day');

[jason_nicholas]

could you please clarify the * operator in the WildMatch? edit- I think I figured it out. * on both sides indicates any position, correct?

Is there a reason I would want to use "LIKE" or "WildMatch" with something as clear as "Rehearsal", as opposed to the more specific "match" needed for the "Set" data? Or are you just offering multiple solutions, with "match" being the best one where specific clarifications are needed?

[sunny_talwar]

From what I understood from your description, the field title can be something like this

title

ahkad abc dhs rehearsal ajdhak

kahdkas jadhkia

jagdjshd rehearsal ajdaldjlas

ajkdhaksdhkarehearsal,ahdklhad

Where rehearsal will be a word within a single cell with other kind of text. The * is a wild card which works with WildMatch and Like to include or exclude (exclude in your case) those rows where the text partially match. If your cell only contain rehearsal, a simple Match() function can also work.

[jason_nicholas]

EDIT- I came up with a solution, which I appended below this updated question. My solution works, but I believe a better understanding of mapping would give me a more Qlik-based solution-

I have a slight change to my requirements, and I was hoping you could help update this for the new details. In the first case, I wanted ONLY the results from Room Code which appear on Room. The code above accomplished that.

This time, I want to retain all Room Code entries from the host data. However, I would like to tag the Room Code entries which match the Room list with some selectable title (lets say GroupA, for now) and also the entries which do not match (GroupB). I want to have a table in my sheet where I can select GroupA to get all of the matching results (the results of the first solution above), or GroupB to get all of the non-matching results.

The Inline Load solution provided for a different matching question of mine will not work here. There I had 11 entries I wanted in 3 groups. Here, I am dealing with hundreds of Room Codes, and around 100 Rooms.

could you describe a code which would tag common entries as GroupA and tag uncommon entries as GroupB?

UPDATED SOLUTION:

In the Excel document housing the 100 (or so) Rooms which I want to identify, I used Concatenate to quickly create the text needed to use the LOAD * INLINE solution to create groups.

LOAD * INLINE [
Space, Room Code
Event Space, 7/Cafeteria

................ (etc, etc)

By doing so, all rooms (like 7/Cafeteria) are grouped under a category called "Event Space", which is selectable from a table called "Space". It works perfectly, but took over 100 lines of code to accomplish. I would appreciate any insight how to clean up my function. Thank you.

[sunny_talwar]

If you have the data in Excel, you don't have to do an Inline Load. You can accomplish the same results with Excel loads as with Inline load. Inline are mostly used where the data is static and small. For data which is 100 rows, I wouldn't use inline as it is not only lengthy, but also difficult to maintain.

Coming back to the code, I have created a small script which will create two groups based on Room Code

Table1:

LOAD [Room Code]

FROM

Community_239740.xlsx

(ooxml, embedded labels, table is Sheet2);

MainTable:

LOAD Student,

    Room,

    If(Exists([Room Code], Room), 'Group 1', 'Group 2') as Group

FROM

Community_239740.xlsx

(ooxml, embedded labels, table is Sheet1);

DROP Table Table1;

Here I created two groups based on if the Room Code matches from Sheet2 with Room in Sheet1 of the Excel sheet

Attaching the Excel and the QVW file for you to play around with.

Best,

Sunny

[jason_nicholas]

That's fantastic. Once I figured out the syntax of the If(Exists code you gave, it made perfect sense. thank you.