# Qlik Sense App Development

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
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

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

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

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

MVP & Luminary

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

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

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

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

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

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

MVP & Luminary

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

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

Highlighted
Specialist

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