Qlik Sense App Development

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for
Did you mean:
Highlighted
Specialist

Auto generate fixed nbr of columns and rows?

Hi All,

There is a floor with the dimension 141 * 50 ( lenght * breath).

I want to create a table that resembles the above floor  wihth 141 columns and 50 rows.

The column should start with A,B........AB,AC.......so on and the rows should start from 1 to 50.

In essence I'm trying to create a grid using a table object

Labels (3)

• Qlik Community

4 Solutions

Accepted Solutions
Highlighted
Luminary

Re: Auto generate fixed nbr of columns and rows?

Are you trying to always explicitly generate the floor diagram with these dimensions? If so then the script below will generate a table called FloorDiagram. This contains the data for your 50 x 141 cell grid.

Then create a Pivot Table as follows:

1. RowNum as the Row Dimension
2. ColCode as the Column Dimension
3. Whatever you need as your measure. I don't know what you want in the cell, so I just used  a constant  as the Measure.
4. Change the Sorting on ColCode to only Sort Numerically, Ascending.

I've attached a partial screen shot of the resulting pivot table and the data and sorting sections.

Here is the script.

CharMap:
Num, Char
01,A
02,B
03,C
04,D
05,E
06,F
07,G
08,H
09,I
10,J
11,K
12,L
13,M
14,N
15,O
16,P
17,Q
18,R
19,S
20,T
21,U
22,V
23,W
24,X
25,Y
26,Z
];

Columns:
1 as Key,
If(RowNo() < 27,
MapSubString('CharMap',
If(RowNo() < 10,
'0' & RowNo(),
RowNo()
)
),
MapSubString('CharMap',
If(Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0) < 10,
'0' & (Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0)),
Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0)
)
) &
MapSubString('CharMap',
If(Mod(RowNo(), 26) < 10,
'0' & (Mod(RowNo(), 26) + If(Mod(RowNo(), 26) = 0, 1, 0)),
Mod(RowNo(), 26)
)
)
) as ColCode,
RowNo() as ColOrder
AutoGenerate 141;

FloorDiagram:
RowNo() as RowNum,
1 as Key
AutoGenerate 50;

FloorDiagram:
Left Join (FloorDiagram)
Key,
Dual(ColCode, ColOrder) as ColCode
Resident Columns;

Drop Field Key From FloorDiagram;

Drop Table Columns;
Highlighted
Luminary

Re: Auto generate fixed nbr of columns and rows?

Are you trying to always explicitly generate the floor diagram with these dimensions? If so then the script below will generate a table called FloorDiagram. This contains the data for your 50 x 141 cell grid.

Then create a Pivot Table as follows:

1. RowNum as the Row Dimension
2. ColCode as the Column Dimension
3. Whatever you need as your measure. I don't know what you want in the cell, so I just used  a constant  as the Measure.
4. Change the Sorting on ColCode to only Sort Numerically, Ascending.

I've attached a partial screen shot of the resulting pivot table.

Here is the script.

CharMap:
Num, Char
01,A
02,B
03,C
04,D
05,E
06,F
07,G
08,H
09,I
10,J
11,K
12,L
13,M
14,N
15,O
16,P
17,Q
18,R
19,S
20,T
21,U
22,V
23,W
24,X
25,Y
26,Z
];

Columns:
1 as Key,
If(RowNo() < 27,
MapSubString('CharMap',
If(RowNo() < 10,
'0' & RowNo(),
RowNo()
)
),
MapSubString('CharMap',
If(Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0) < 10,
'0' & (Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0)),
Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0)
)
) &
MapSubString('CharMap',
If(Mod(RowNo(), 26) < 10,
'0' & (Mod(RowNo(), 26) + If(Mod(RowNo(), 26) = 0, 1, 0)),
Mod(RowNo(), 26)
)
)
) as ColCode,
RowNo() as ColOrder
AutoGenerate 141;

FloorDiagram:
RowNo() as RowNum,
1 as Key
AutoGenerate 50;

FloorDiagram:
Left Join (FloorDiagram)
Key,
Dual(ColCode, ColOrder) as ColCode
Resident Columns;

Drop Field Key From FloorDiagram;

Drop Table Columns;
Highlighted
Luminary

Re: Auto generate fixed nbr of columns and rows?

Here is the attachment. The editor wasn't letting me add it to the prior post.

Highlighted
MVP & Luminary

Re: Auto generate fixed nbr of columns and rows?

If you don't need just a two column-table which you expand within a pivot else a real table within the datamodel you could use something like this:

t1: load *, 1 as ID, chr(64 + if(Cluster = 1, null(), Cluster -1)) &
chr(64 + recno() - ((Cluster - 1) * 26)) as Column; load ceil(recno() / 26) as Cluster autogenerate 141; t2: generic load 1 as ID, recno() as Row, 'X' as Dummy autogenerate 50; for i = nooftables() to 0 step -1 let vTable = tablename(\$(i)); if left('\$(vTable)', 2) = 't2' then left join (t1) load * resident \$(vTable); drop tables \$(vTable); end if next

- Marcus

12 Replies
Highlighted
Creator II

Re: Auto generate fixed nbr of columns and rows?

Hi,

You can use a Synthetic dimension with ValueLoop expression to create the grid.
More o less is like a waffle chart like this https://qvdesign.wordpress.com/2012/02/16/new-qlikview-proportional-waffle-charts/

Other solution will be generate the set of data in the script using a loop.

Ag+
Ag+
Highlighted
Luminary

Re: Auto generate fixed nbr of columns and rows?

Are you trying to always explicitly generate the floor diagram with these dimensions? If so then the script below will generate a table called FloorDiagram. This contains the data for your 50 x 141 cell grid.

Then create a Pivot Table as follows:

1. RowNum as the Row Dimension
2. ColCode as the Column Dimension
3. Whatever you need as your measure. I don't know what you want in the cell, so I just used  a constant  as the Measure.
4. Change the Sorting on ColCode to only Sort Numerically, Ascending.

I've attached a partial screen shot of the resulting pivot table and the data and sorting sections.

Here is the script.

CharMap:
Num, Char
01,A
02,B
03,C
04,D
05,E
06,F
07,G
08,H
09,I
10,J
11,K
12,L
13,M
14,N
15,O
16,P
17,Q
18,R
19,S
20,T
21,U
22,V
23,W
24,X
25,Y
26,Z
];

Columns:
1 as Key,
If(RowNo() < 27,
MapSubString('CharMap',
If(RowNo() < 10,
'0' & RowNo(),
RowNo()
)
),
MapSubString('CharMap',
If(Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0) < 10,
'0' & (Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0)),
Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0)
)
) &
MapSubString('CharMap',
If(Mod(RowNo(), 26) < 10,
'0' & (Mod(RowNo(), 26) + If(Mod(RowNo(), 26) = 0, 1, 0)),
Mod(RowNo(), 26)
)
)
) as ColCode,
RowNo() as ColOrder
AutoGenerate 141;

FloorDiagram:
RowNo() as RowNum,
1 as Key
AutoGenerate 50;

FloorDiagram:
Left Join (FloorDiagram)
Key,
Dual(ColCode, ColOrder) as ColCode
Resident Columns;

Drop Field Key From FloorDiagram;

Drop Table Columns;

Highlighted
Luminary

Re: Auto generate fixed nbr of columns and rows?

Are you trying to always explicitly generate the floor diagram with these dimensions? If so then the script below will generate a table called FloorDiagram. This contains the data for your 50 x 141 cell grid.

Then create a Pivot Table as follows:

1. RowNum as the Row Dimension
2. ColCode as the Column Dimension
3. Whatever you need as your measure. I don't know what you want in the cell, so I just used  a constant  as the Measure.
4. Change the Sorting on ColCode to only Sort Numerically, Ascending.

I've attached a partial screen shot of the resulting pivot table and the data and sorting sections.

Here is the script.

CharMap:
Num, Char
01,A
02,B
03,C
04,D
05,E
06,F
07,G
08,H
09,I
10,J
11,K
12,L
13,M
14,N
15,O
16,P
17,Q
18,R
19,S
20,T
21,U
22,V
23,W
24,X
25,Y
26,Z
];

Columns:
1 as Key,
If(RowNo() < 27,
MapSubString('CharMap',
If(RowNo() < 10,
'0' & RowNo(),
RowNo()
)
),
MapSubString('CharMap',
If(Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0) < 10,
'0' & (Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0)),
Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0)
)
) &
MapSubString('CharMap',
If(Mod(RowNo(), 26) < 10,
'0' & (Mod(RowNo(), 26) + If(Mod(RowNo(), 26) = 0, 1, 0)),
Mod(RowNo(), 26)
)
)
) as ColCode,
RowNo() as ColOrder
AutoGenerate 141;

FloorDiagram:
RowNo() as RowNum,
1 as Key
AutoGenerate 50;

FloorDiagram:
Left Join (FloorDiagram)
Key,
Dual(ColCode, ColOrder) as ColCode
Resident Columns;

Drop Field Key From FloorDiagram;

Drop Table Columns;
Highlighted
Luminary

Re: Auto generate fixed nbr of columns and rows?

Are you trying to always explicitly generate the floor diagram with these dimensions? If so then the script below will generate a table called FloorDiagram. This contains the data for your 50 x 141 cell grid.

Then create a Pivot Table as follows:

1. RowNum as the Row Dimension
2. ColCode as the Column Dimension
3. Whatever you need as your measure. I don't know what you want in the cell, so I just used  a constant  as the Measure.
4. Change the Sorting on ColCode to only Sort Numerically, Ascending.

I've attached a partial screen shot of the resulting pivot table and the data and sorting sections.

Here is the script.

CharMap:
Num, Char
01,A
02,B
03,C
04,D
05,E
06,F
07,G
08,H
09,I
10,J
11,K
12,L
13,M
14,N
15,O
16,P
17,Q
18,R
19,S
20,T
21,U
22,V
23,W
24,X
25,Y
26,Z
];

Columns:
1 as Key,
If(RowNo() < 27,
MapSubString('CharMap',
If(RowNo() < 10,
'0' & RowNo(),
RowNo()
)
),
MapSubString('CharMap',
If(Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0) < 10,
'0' & (Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0)),
Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0)
)
) &
MapSubString('CharMap',
If(Mod(RowNo(), 26) < 10,
'0' & (Mod(RowNo(), 26) + If(Mod(RowNo(), 26) = 0, 1, 0)),
Mod(RowNo(), 26)
)
)
) as ColCode,
RowNo() as ColOrder
AutoGenerate 141;

FloorDiagram:
RowNo() as RowNum,
1 as Key
AutoGenerate 50;

FloorDiagram:
Left Join (FloorDiagram)
Key,
Dual(ColCode, ColOrder) as ColCode
Resident Columns;

Drop Field Key From FloorDiagram;

Drop Table Columns;
Highlighted
Luminary

Re: Auto generate fixed nbr of columns and rows?

Are you trying to always explicitly generate the floor diagram with these dimensions? If so then the script below will generate a table called FloorDiagram. This contains the data for your 50 x 141 cell grid.

Then create a Pivot Table as follows:

1. RowNum as the Row Dimension
2. ColCode as the Column Dimension
3. Whatever you need as your measure. I don't know what you want in the cell, so I just used  a constant  as the Measure.
4. Change the Sorting on ColCode to only Sort Numerically, Ascending.

I've attached a partial screen shot of the resulting pivot table.

Here is the script.

CharMap:
Num, Char
01,A
02,B
03,C
04,D
05,E
06,F
07,G
08,H
09,I
10,J
11,K
12,L
13,M
14,N
15,O
16,P
17,Q
18,R
19,S
20,T
21,U
22,V
23,W
24,X
25,Y
26,Z
];

Columns:
1 as Key,
If(RowNo() < 27,
MapSubString('CharMap',
If(RowNo() < 10,
'0' & RowNo(),
RowNo()
)
),
MapSubString('CharMap',
If(Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0) < 10,
'0' & (Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0)),
Floor(RowNo() / 26) - If(Mod(RowNo(), 26) = 0, 1, 0)
)
) &
MapSubString('CharMap',
If(Mod(RowNo(), 26) < 10,
'0' & (Mod(RowNo(), 26) + If(Mod(RowNo(), 26) = 0, 1, 0)),
Mod(RowNo(), 26)
)
)
) as ColCode,
RowNo() as ColOrder
AutoGenerate 141;

FloorDiagram:
RowNo() as RowNum,
1 as Key
AutoGenerate 50;

FloorDiagram:
Left Join (FloorDiagram)
Key,
Dual(ColCode, ColOrder) as ColCode
Resident Columns;

Drop Field Key From FloorDiagram;

Drop Table Columns;
Highlighted
Luminary

Re: Auto generate fixed nbr of columns and rows?

Here is the attachment. The editor wasn't letting me add it to the prior post.

Highlighted
MVP & Luminary

Re: Auto generate fixed nbr of columns and rows?

If you don't need just a two column-table which you expand within a pivot else a real table within the datamodel you could use something like this:

t1: load *, 1 as ID, chr(64 + if(Cluster = 1, null(), Cluster -1)) &
chr(64 + recno() - ((Cluster - 1) * 26)) as Column; load ceil(recno() / 26) as Cluster autogenerate 141; t2: generic load 1 as ID, recno() as Row, 'X' as Dummy autogenerate 50; for i = nooftables() to 0 step -1 let vTable = tablename(\$(i)); if left('\$(vTable)', 2) = 't2' then left join (t1) load * resident \$(vTable); drop tables \$(vTable); end if next

- Marcus

Highlighted
Specialist

Re: Auto generate fixed nbr of columns and rows?

Thank you Guys..appreciate your help.I'll  get back to you all after testing the code

Highlighted
Specialist

Re: Auto generate fixed nbr of columns and rows?

Worked like a charm. Thank you  guys for the help.