Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
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)
4 Solutions

Accepted Solutions
Highlighted
Luminary
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:
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;

View solution in original post

Highlighted
Luminary
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:
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;

View solution in original post

Highlighted
Luminary
Luminary

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

View solution in original post

Highlighted
MVP & Luminary
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

View solution in original post

12 Replies
Highlighted
Creator II
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
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:
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;

 

 

Highlighted
Luminary
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:
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;
Highlighted
Luminary
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:
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;

View solution in original post

Highlighted
Luminary
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:
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;

View solution in original post

Highlighted
Luminary
Luminary

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

View solution in original post

Highlighted
MVP & Luminary
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

View solution in original post

Highlighted
Specialist
Specialist

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

Highlighted
Specialist
Specialist

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