topic Re: Making excel column names to fields(filter pane list) and get linked to the original data. in App Development

Making excel column names to fields(filter pane list) and get linked to the original data.

nezuko_kamado — Thu, 23 May 2024 05:39:44 GMT

I have data has column names of pick1, pick2, ..., pick5. But I want to make a new list, 'pick' and put these five columns as factors of 'pick', so that I can create a filter pane and get linked. Same as cost1, cost2, cost3, cost4 should belong to a new field, 'cost' , and get linked to the main table.
Now I click the filter panes, but nothing gets selected.

mainTable: load*inline [ week,'num of pick, pick1, pick2, pick3, pick4, pick5, cost1, cost2, cost3, cost4, hrSum 1, 3, apple, banana, strawberry, NA, NA, 10, 20, 30, 40, 200 2, 5, durian, grapes, apple, dragonfruit,orange, 20, 25, 0, 20, 300 3, 1, fig, NA, NA, NA, NA, 5, 10, 0, 15, 500 4, 2, lemon, gratefruit,NA, NA, NA, 10, 3, 5, 0, 1000 5, 3, dragonfruit, gratefruit, fig, NA, NA, 10, 20, 5, 4, 200]; colname1: load * inline [ pick, pick1, pick2, pick3, pick4, pick5]; colname2: load * inline [ cost cost1 cost2 cost3 cost4];

Re: Making excel column names to fields(filter pane list) and get linked to the original data.

marcus_sommer — Thu, 23 May 2024 15:27:37 GMT

Just transform your data per: The Crosstable Load - Qlik Community - 1468083

Re: Making excel column names to fields(filter pane list) and get linked to the original data.

nezuko_kamado — Fri, 24 May 2024 20:29:47 GMT

But "pick_type" that I created in 'Final data' (cross table) is not linked to the main table's pick1, pick2,.... pick5

mainTable: load*inline [ week,'num of pick, pick1, pick2, pick3, pick4, pick5, cost1, cost2, cost3, cost4, hrSum 1, 3, apple, banana, strawberry, NA, NA, 10, 20, 30, 40, 200 2, 5, durian, grapes, apple, dragonfruit,orange, 20, 25, 0, 20, 300 3, 1, fig, NA, NA, NA, NA, 5, 10, 0, 15, 500 4, 2, lemon, gratefruit,NA, NA, NA, 10, 3, 5, 0, 1000 5, 3, dragonfruit, gratefruit, fig, NA, NA, 10, 20, 5, 4, 200]; tmpData: Crosstable (pick, fruits) Load week, pick1, pick2, pick3, pick4, pick5 Resident mainTable; Final: Load week, pick as pick_type, fruits Resident tmpData; Drop Table tmpData;

But

Re: Making excel column names to fields(filter pane list) and get linked to the original data.

MeehyeOh — Mon, 27 May 2024 02:08:55 GMT

Hi @nezuko_kamado

The values that you can select from the filter are those that belong to the field.

So filtering according to your requirements, you need Crosstable.

1) Load Script

mainTab_TMP:

load*inline [

week,'num of pick, pick1, pick2, pick3, pick4, pick5, cost1, cost2, cost3, cost4, hrSum

1, 3, apple, banana, strawberry, NA, NA, 10, 20, 30, 40, 200

2, 5, durian, grapes, apple, dragonfruit,orange, 20, 25, 0, 20, 300

3, 1, fig, NA, NA, NA, NA, 5, 10, 0, 15, 500

4, 2, lemon, gratefruit,NA, NA, NA, 10, 3, 5, 0, 1000

5, 3, dragonfruit, gratefruit, fig, NA, NA, 10, 20, 5, 4, 200];

Pick:

CrossTable(Pick,Pick_Value,2)

Load

week,

['num of pick],

pick1,

pick2,

pick3,

pick4,

pick5

Resident mainTab_TMP;

Cost:

CrossTable(Cost, Cost_Value, 1)

Load

week,

cost1,

cost2,

cost3,

cost4

Resident mainTab_TMP;

hr:

Load

week,

hrSum

Resident mainTab_TMP;

Drop Table mainTab_TMP;

2) Visualizaion

Please zoom and check the image.

Re: Making excel column names to fields(filter pane list) and get linked to the original data.

williamsmithjoul12 — Tue, 28 May 2024 08:04:05 GMT

It seems like you're trying to organize your data effectively. Have you considered using Excel's "Transpose" feature to reorganize your columns into rows? This could help streamline and current county your filter pane setup and data linking process.

Re: Making excel column names to fields(filter pane list) and get linked to the original data.

marcus_sommer — Wed, 29 May 2024 06:17:22 GMT

That's mainly the aim because within the most scenarios the origin source-table isn't needed anymore and therefore removed. In cases in which a source is intentionally divided into several tables you could a unique key-field and if non exists you may create one with recno() in all remaining tables.

I'm quite sure that you don't need a link between the crosstable-columns and the transposed rows. Just play with the transformed data in the UI and I think you will be able to get all needed views.