Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jonstokes
New Contributor II

Applying selections to a single field with multiple responses

Hello!

I'm trying to visualize data where one dimension in the data file is split across three separate fields. To give some context, I work in international development and any given project can be paid for by a combination of different Funders.


This means I have the following fields in my data file:


[Funder 1]

[Funder 2] (sometimes blank)

[Funder 3] (often blank)

Depending on how the data has been entered, a specific "Funder X" could appear in any of the three Funder fields above.

As an end result, in the visualization I'd like a master list of each distinct Funder, which I can use to make selections across all three Funder fields. So selecting "Funder X" will choose records where "Funder X" appears in any of the three Funder fields. Is this possible?

The closest I have been able to come so far is merging the fields together in the load script as below.

  "Funder 1" & ' ' &  "Funder 2"  & ' ' & "Funder 3"  as Funder ,

Although this works though search, the Funder list is messy and not that easy to work with.

Any help would be much appreciated!


Thanks,

Jonathan

1 Solution

Accepted Solutions

Re: Applying selections to a single field with multiple responses

Load up the spreadsheet into a single table like:

ProjectData:

LOAD

  Record,    Country,    Location,    Project, 

  [Funder 1],    [Funder 2],    [Funder 3],    Category

FROM

ProjectData.xlsx

(ooxml, embedded labels, table is Sheet1);

Then use a CrossTable load to build a linked table of Funders using the already loaded table as input.

Funders:

CrossTable(FunderSeq, Funder, 1)

LOAD Record,

     [Funder 1],

     [Funder 2],

     [Funder 3]

RESIDENT ProjectData;

DROP FIELD FunderSeq;

Your final model will look like this:

2016-06-02_17-47-29.png

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

13 Replies
MVP
MVP

Re: Applying selections to a single field with multiple responses

May be using the link table:

MasterList:

LOAD [Funder 1],

          [Funder 1] as Funder

Resident ...;

Concatenate (MasterList)

LOAD [Funder 2],

          [Funder 2] as Funder

Resident ...;

Concatenate (MasterList)

LOAD [Funder 3],

          [Funder 3] as Funder

Resident ...;

gompa786
Contributor III

Re: Applying selections to a single field with multiple responses

Yeah I also agree with Sunny In my opinion in whoever way you are getting all the 3 funders as source from different files or same file different tabs but the idea is to load them and concatenate them into one dataset to be used in your charts..

Thanks

jonstokes
New Contributor II

Re: Applying selections to a single field with multiple responses

Thanks Sunny!

I'm trying to add the above code into the load script, but it's causing errors. Do I need to adapt it before inserting it? Is this creating a MasterList or do I need to do this somewhere else?

The three Funder fields are just columns in the same spreadsheet.

Thanks,

Jonathan

gompa786
Contributor III

Re: Applying selections to a single field with multiple responses

Hi Jonathan,

If all the three funders are from the same spread sheet your thought is correct

Funder  1 & Funder 2  & Funder 3  as Funder , in the dimension.. Please make sure at any given point of time

you will have only one value populated in any of the 3 funders then that should work for you..

Thanks

jonstokes
New Contributor II

Re: Applying selections to a single field with multiple responses

Unfortunately, as projects can be funded by multiple donors all three Funder fields in a given row can be populated, so my initial attempt doesn't work very well. Trying to manipulate the Excel table to get just one funder per row would mean I'd have to duplicate a lot of the data which would distort the totals.

I think the first suggestion of a resident table/link table within Sense is the way to go, I'm just not sure how to go about this.

Thanks

isaaclin
New Contributor III

Re: Applying selections to a single field with multiple responses

If all columns on same spreadsheet, I will suggest use Power Query in Excel to transfor myour data, use qlik pulling data from power query instead of pulling from excel spread sheet.

gompa786
Contributor III

Re: Applying selections to a single field with multiple responses

HI Jonathan,

Could just put a screen shot of how your source file looks like atleast with 5 columns I will write down the complete logic and you can copy paste..

Thanks

jonstokes
New Contributor II

Re: Applying selections to a single field with multiple responses

Source table screenshot.png

This is my source table, with the Funder columns in red. File is called "Beneficiaries tracker (master file)".

I hope it will be possible to keep the source table in this format and setup a resident table in QlikSense? Ideally I would keep the source file as it is, as I have reports designed to output into it each quarter.

isaaclin
New Contributor III

Re: Applying selections to a single field with multiple responses

I have two ways to do it

1. Load the table with all the funders fields,and then load this table 3 time with only on funder field in the table  and then concatenate these three tables, drop the first table.

2. as I mentioned before, you can use a function called Power Query in Excel to transform your table to the way you need. it won't change your original table layout. google Power Query. I use it  a lot to tranform some excel files to qlik firendly format.