topic Re: How to populate table data in table in QlikView

How to populate table data in table

ashis — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

I have two tables , having 100 columns each.

I want a list box that would show the table names and when click on the table names , we need table data to be populated in the straight table or Pivot table.

Could you please suggest how to get this done.

Re: How to populate table data in table

sunny_talwar — Mon, 12 Jun 2017 11:26:45 GMT

May be you are looking for something like this

Customizable Straight Table

Re: How to populate table data in table

kamal_sanguri — Mon, 12 Jun 2017 11:33:59 GMT

If you are looking for something to convert a straight table to pivot or vice- versa. Then use the fast change option of charts.

Please see the attached.

By clicking on highlighted button chart will change to pivot and then again to straight chart.

Re: How to populate table data in table

RonaldDoes — Mon, 12 Jun 2017 11:37:15 GMT

Hi Ashis,

Like the following example?

Gyazo - e75c1effb255f0cfe2f254258b73cc18.gif

Hope this helps you.

With kind regards,

Ronald

Re: How to populate table data in table

Peter_Cammaert — Mon, 12 Jun 2017 11:45:06 GMT

A suggestion:

In your script, create an inline LOAD that creates an unconnected table with one field containing two values: the names of your two tables. Let's assume that you call this field TableToShow. Reload.
Create a listbox that displays the values of field TableToShow. Select a single value and in Object Properties->General, enable 'Always one selected Value'.
Now create two straight tables, each showing the 100 columns of a different internal table or whatever you need to be visible. Place them on top of each other and resize them so that they match.
For the first straight table, open Object Properties->Layout and select 'Conditional' in the Show group and enter an expression like:
TableToShow='table1name'
where you replace table1name with the value you loaded in TableToShow in your INLINE table. Do the same for the second table.

Depending on the selection you make in your listbox, only one straight table will be shown at a time.

Best,

Peter

Re: How to populate table data in table

ashis — Mon, 12 Jun 2017 14:04:39 GMT

Thank you Peter for your reply.

It is working as you suggested.

However in real scenario i have around 150 columns in one table , so manually adding this columns in the straight table is difficult when we talk about two tables. as per your suggestion below:

>Now create two straight tables, each showing the 100 columns of a different internal table or whatever you need to be visible. Place them on top of each other and resize them so that they match.

Is there any way i can populate the field values instead of manually adding them in the dimension tab.

Thank you,

Ashis

Re: How to populate table data in table

ashis — Mon, 12 Jun 2017 14:06:41 GMT

Hi Sunny,

Thank you for your quick reply. I am looking similar concept however differently. i need all the fields value in the straight table when choose the name of the table from list box. I will only have table name list box.

Thank you,

Ashis

Re: How to populate table data in table

ashis — Mon, 12 Jun 2017 14:07:47 GMT

Thanks Kamal for your reply, however i am not looking to convert straight table to pivot or vice versa.

Re: How to populate table data in table

sunny_talwar — Mon, 12 Jun 2017 14:08:17 GMT

You might be able to use $Field and $Table to get this to work.... for more help, would you be able to share a sample to take a look?

Re: How to populate table data in table

ashis — Mon, 12 Jun 2017 14:10:52 GMT

Hi Ronald,

Yes , similar output, I will have list box only having names of the table and once click all the data of the table would be in straight table.

Thank you,

Ashis

Re: How to populate table data in table

ashis — Tue, 13 Jun 2017 05:30:54 GMT

Hi Sunny,

Lets take an example , i have two tables Sales and Customer. Now i have created one table name Table_to_show, there i have kept all the table names that are there in my data model.

Now i want to create a list box on Tables, that would list all the tables present , when when user click on the table name the data should be populated in straight table.

I do not want to add manually all the fields in the table in the dimension tab. ( because in the real scenario the columns are more so chances are more to do mistake) .

Could you please tell me how to get data populated with the help of $Field and $Table name.

Sales:

LOAD * INLINE [

Country, Sale

USA, 10000

UK, 52220

Germany, 69000

];

Customer:

LOAD * INLINE [

Customer_id, Name

1, Peter

2, John

3, Micheal

];

Table_to_show:

LOAD * INLINE [

Tables

Sales

Customer

];

Thank you,

Ashis

Re: How to populate table data in table

sunny_talwar — Tue, 13 Jun 2017 07:50:52 GMT

You don't need to create a table which lists all the names, QlikView already provides you these tables -> $Table, $Field. You can view them by checking 'Show System Fields' on the Fields tab of the Sheet Properties window

Now I am not fully sure how your chart is created, but I created two dimensions using the dollar sign expansion

=$(='[' & SubField(Concat($Field, '|'), '|', 1) & ']')

=$(='[' & SubField(Concat($Field, '|'), '|', 2) & ']')

Based on your selection in $Table, the dimensions will change

When Customer is selected

When Sales is selected

I have used 1 as expression and hide the expression from the presentation tab.

I understand that this might not be perfect, but since I don't really know the kind of straight table you are hoping to get, this is the best I can suggest until I know more.

Re: How to populate table data in table

ashis — Tue, 13 Jun 2017 08:03:32 GMT

Thank you, I wanted similar thing like the one you did.

Regards,

Ashis Sau

Re: How to populate table data in table

ashis — Tue, 13 Jun 2017 08:07:20 GMT

So if i have 100 columns in a table i need to create calculated dimensions using the same syntax for 100 times correct ?

=$(='[' & SubField(Concat($Field, '|'), '|', 1) & ']')

=$(='[' & SubField(Concat($Field, '|'), '|', 2) & ']')

=$(='[' & SubField(Concat($Field, '|'), '|',n) & ']')

Re: How to populate table data in table

sunny_talwar — Tue, 13 Jun 2017 08:09:35 GMT

Yes, unfortunately you will have to do that. Create this so that it can accommodate the max dimensions across all tables. So, for example if you customer have 2 and Sales have 3 columns, then you have to create 3 of them. In addition you have to make sure that the third dimension only shows when Sales is selected using enable conditions.