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

[nezuko_kamado]

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];

[marcus_sommer]

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

[nezuko_kamado]

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  

[MeehyeOh]

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.

[williamsmithjoul12]

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.

[marcus_sommer]

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.