topic Re: Auto generate fixed nbr of columns and rows? in App Development

Auto generate fixed nbr of columns and rows?

swarup_malli — Sat, 16 Nov 2024 21:44:58 GMT

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

Re: Auto generate fixed nbr of columns and rows?

albert_guito — Tue, 11 Dec 2018 06:41:19 GMT

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+

Re: Auto generate fixed nbr of columns and rows?

lblumenfeld — Tue, 11 Dec 2018 06:50:26 GMT

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:

RowNum as the Row Dimension
ColCode as the Column Dimension
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.
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:
Mapping Load * Inline [
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:
Load 
    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:
Load
    RowNo() as RowNum,
    1 as Key
AutoGenerate 50;

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

Drop Field Key From FloorDiagram;

Drop Table Columns;

Re: Auto generate fixed nbr of columns and rows?

lblumenfeld — Tue, 11 Dec 2018 06:52:09 GMT

Then create a Pivot Table as follows:

RowNum as the Row Dimension
ColCode as the Column Dimension
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.
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:
Mapping Load * Inline [
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:
Load 
    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:
Load
    RowNo() as RowNum,
    1 as Key
AutoGenerate 50;

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

Drop Field Key From FloorDiagram;

Drop Table Columns;

Re: Auto generate fixed nbr of columns and rows?

lblumenfeld — Tue, 11 Dec 2018 06:57:56 GMT

Then create a Pivot Table as follows:

RowNum as the Row Dimension
ColCode as the Column Dimension
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.
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:
Mapping Load * Inline [
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:
Load 
    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:
Load
    RowNo() as RowNum,
    1 as Key
AutoGenerate 50;

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

Drop Field Key From FloorDiagram;

Drop Table Columns;

Re: Auto generate fixed nbr of columns and rows?

lblumenfeld — Tue, 11 Dec 2018 07:00:44 GMT

Then create a Pivot Table as follows:

RowNum as the Row Dimension
ColCode as the Column Dimension
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.
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:
Mapping Load * Inline [
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:
Load 
    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:
Load
    RowNo() as RowNum,
    1 as Key
AutoGenerate 50;

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

Drop Field Key From FloorDiagram;

Drop Table Columns;

Re: Auto generate fixed nbr of columns and rows?

lblumenfeld — Tue, 11 Dec 2018 07:01:35 GMT

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

Re: Auto generate fixed nbr of columns and rows?

marcus_sommer — Tue, 11 Dec 2018 09:10:51 GMT

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

Re: Auto generate fixed nbr of columns and rows?

swarup_malli — Tue, 11 Dec 2018 15:49:13 GMT

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

Re: Auto generate fixed nbr of columns and rows?

swarup_malli — Tue, 11 Dec 2018 17:56:46 GMT

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

Re: Auto generate fixed nbr of columns and rows?

swarup_malli — Wed, 12 Dec 2018 01:27:57 GMT

In the generated series i do not see AZ , BZ,CZ.....IZ..

Instead AY is followed by AA .. same for BY which is followed by BA.

Re: Auto generate fixed nbr of columns and rows?

marcus_sommer — Wed, 12 Dec 2018 08:09:36 GMT

For me it worked:

If you are missing the Z's then it might be caused from differences between my default regional-settings (OS) and/or the used char-sets respectively collation-settings and your configurations. Maybe you could adjust them in any way.

Deducing from this it indicates that in your char-list is any additional/different char compared to my "standard" alphabet. To know it might be useful for any adjusting approaches to the settings.

An alternatively might be just to increase the number of chars by using 27 instead of 26, like:

t1: load *, 1 as ID, chr(64 + if(Cluster = 1, null(), Cluster -1)) & 
                     chr(64 + recno() - ((Cluster - 1) * 27)) as Column;
    load ceil(recno() / 27) as Cluster autogenerate 141;

- Marcus

Re: Auto generate fixed nbr of columns and rows?

swarup_malli — Wed, 12 Dec 2018 14:45:23 GMT

Thank you again Marcus!