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