Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Table Creation

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

1 Solution

Accepted Solutions

Re: Table Creation

See attached example


talk is cheap, supply exceeds demand
16 Replies

Re: Table Creation

I'm afraid I don't understand your problem at all. Please read this document: QlikCommunity Tip: How to get answers to your post


talk is cheap, supply exceeds demand
Employee
Employee

Re: Table Creation

What i suggest is to give the 1st column a name. Instead of "DATA NEEDED" , type "MEASURE NAME" or something like that.

Capture.PNG

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))

;

Not applicable

Re: Table Creation

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

Re: Table Creation

You can load all three files with the Quick Data Load wizard. See this video: Qlik Sense Desktop - Using Quick Data Load (video)


talk is cheap, supply exceeds demand
Not applicable

Re: Table Creation

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 nameValues
Id
first_name
last_name
email
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

Re: Table Creation

See attached example


talk is cheap, supply exceeds demand
Not applicable

Re: Table Creation

Thanks a lot Gysbert wassenaar...

Not applicable

Re: Table Creation

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

Re: Table Creation

Try this expression as measure: fieldvalue($Field, fieldindex('id',only(id)))


talk is cheap, supply exceeds demand
Community Browser