Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I need to create a table with 2 columns using QLIKSENSE.
My requirement is first column is hardcoded. i have attached sample excel file how it should look.
For that hardcoded value we have dynamic data in database. That dynamic data should place in second column.
anyone please help to prepare table as i needed.
Thanks,
Pramod
I'm afraid I don't understand your problem at all. Please read this document: QlikCommunity Tip: How to get answers to your post
What i suggest is to give the 1st column a name. Instead of "DATA NEEDED" , type "MEASURE NAME" or something like that.
Then load these values from your spreadsheet like this
DataTable:
Load
"Measure Name"
from <FolderConnection> ;
Then right you can load the related data from your database (as long as you have the same 'measure name' field in the database
left join (DataTable)
Load
"Database Measure Name Field" as "Measure Name",
"Database Measure Value" as "Values"
SQL ........
From <DatabaseConnection>;
That WILL work but it also means you are bringing back ALL the values from the database and then filtering them in QLIK which would be perceived as inefficient.
So i suggest passing the list of values into the SQL where clause like this:
//Read in the 1st colum values from the spreadsheet
Values:
Load
"Measure Name"
from <FolderConnection> ;
//create a variable that stores all values as a comma delimitted list ...'ABC','DEF','GHI'
DelimittedValueList:
load
chr(39) & concat(distinct [MeasureName], chr(39) & ',' & chr(39)) & chr(39) as ValueList
resident Values;
let vList=peek('ValueList',0,'DelimittedValueList');
//Connect to the database
LIB CONNECT TO 'DatabaseConnection';
//Load in only the values that match from the database using ('ABC','DEF','GHI')
LOAD `Salesman ID`,
Salesman;
SQL SELECT
"Measure Name from Database",
"Measure Value from Database"
FROM <DatabaseConnection>
where 'Measure Name from Database` in ($(vList))
;
Hi Gysbert Wassenaar,
Sorry for incomplete data.
Now i am attaching the samples of 3 excel files consisting data of 3 tables.
I need all the data of 3 tables in single table. where column names of all the tables should come in first column of the table.
and i will add filterpane with dimension as "id" when i select one id in filter pane it should display the data regarding to that user.
Thanks,
Pramod
You can load all three files with the Quick Data Load wizard. See this video: Qlik Sense Desktop - Using Quick Data Load (video)
Hi Gysbert wassenaar,
Those csv files are sample data. I need to load those data from the database.
Those csv files are the data what i have in database.
Now i need to create a table in qlik sense sheet. The table must have 2 columns.
The first column must have all the fields name from the above 3 csv files like below.
Meausure name | Values |
---|---|
Id | |
first_name | |
last_name | |
country | |
city | |
language | |
Monthly Income | |
Monthly Expenses | |
Individual Expense Value | |
Projected Number of Months Needed | |
Completion (boolean) | |
Date | |
time | |
After creation of table with these fields. i need to add a filter pane with dimension "Id" in the same sheet.
when i select some id number in the filter pane. the data related to that id should be displayed in the second column i.e "Values" column.
I think you have understand my problem. please help to sort out this.
Thanks,
Pramod
See attached example
Thanks a lot Gysbert wassenaar...
Hi Gysbert wassenaar,
The above solution works fine if we have data in order like 1,2,3,4,5.. so on.
but in my database the data is not in order. i have like 1,2, 7,8,9 like that.
don't have data related to id 3,4,5.
please help with any other solution,
Thanks,
Pramod
Try this expression as measure: fieldvalue($Field, fieldindex('id',only(id)))