topic Re: How to create Dynamic Table Box in QlikView

How to create Dynamic Table Box

Anonymous — Fri, 09 Mar 2018 04:31:24 GMT

I have source like this

ID	Date	Amount
1	1-Jan-18	10000
1	2-Jan-18	20000
1	3-Jan-18	30000
1	4-Jan-18	40000
1	5-Jan-18	50000
2	5-Jan-18	70000

The goal is like this

ID	1-Jan-18	2-Jan-18	3-jan-18	4-Jan-18	5-Jan-18
1	10000	20000	30000	40000	50000
2					70000

i use generic load but the data separated for each date. the problem is how to union them to one table and create dynamic table box or straight table without manually add new date field to table box properties on presentation layer, because user will add new data daily and didn't know how use qlikview, they know only reload and export excel.

Please Answer this without qvw file, because i use Personal Edition. Thanks

Re: How to create Dynamic Table Box

isingh30 — Sun, 11 Mar 2018 18:02:24 GMT

The Generic Load

Re: How to create Dynamic Table Box

isingh30 — Sun, 11 Mar 2018 18:03:25 GMT

Can you share your data or app?

Re: How to create Dynamic Table Box

isingh30 — Sun, 11 Mar 2018 18:11:08 GMT

Transform your data in straight table. You have to use set statement in main script. Please define file path in the set statement.Use '*' in the file name at the end of the load statement. This will fetch every file starts with the name & automatically update the data.

Re: How to create Dynamic Table Box

swuehl — Sun, 11 Mar 2018 18:27:10 GMT

Maybe just read your data in like shown in your first table, no transformations.

Then use a pivot table with two dimensions ID and Date, Date pivoted to the top. As Expression maybe just

=Only(Amount)

If new data comes in, this will automatically be reflected in your chart.

Re: How to create Dynamic Table Box

isingh30 — Sun, 11 Mar 2018 18:31:09 GMT

I didn't understand this - If new data comes in, this will automatically be reflected in your chart. Request you to please explain a bit. Thank you!

Re: How to create Dynamic Table Box

swuehl — Sun, 11 Mar 2018 18:37:53 GMT

If you don't transform the data tables using GENERIC LOAD, new data records will just be added to the table shown as first table in the OP, no new tables / fields are generated.

New data for new dates are hence just new field values, so using a pivot table will just add the new data to the dimensional values, without the need to add a new field as dimension or expression.

IMO, this is how we should build the charts (no need to change the dimensions / expressions when new data is loaded).

Re: How to create Dynamic Table Box

Anonymous — Mon, 12 Mar 2018 08:59:24 GMT

i'm success now union all table from generic load to one table, but how i can automaticaly add new field(Date) as column to straight table without add it from properties, i try it on calculated dimension or expression with subfield() but it just add spesific date base on index

Re: How to create Dynamic Table Box

Anonymous — Mon, 12 Mar 2018 09:05:23 GMT

This good Solution, but when i drag the date field

the teble box will be like this

ID	Date	1-Jan-18	2-Jan-18	3-jan-18	4-Jan-18	5-Jan-18
1		10000	20000	30000	40000	50000
2						70000

how can i achieve this

ID	1-Jan-18	2-Jan-18	3-jan-18	4-Jan-18	5-Jan-18
1	10000	20000	30000	40000	50000
2					70000

Re: How to create Dynamic Table Box

isingh30 — Tue, 13 Mar 2018 17:17:13 GMT

Your data source is flat file?

Re: How to create Dynamic Table Box

swuehl — Tue, 13 Mar 2018 17:40:17 GMT

To hide the Date dimension label, you can grab the right border of the label with the mouse and make the size of the Date label minimal. Then set the label of the Date dimension on dimension tab to

=''

(empty string, two single quotes)