Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
swarup_malli
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 (2)
4 Solutions

Accepted Solutions
lblumenfeld
Partner Ambassador
Partner Ambassador

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

lblumenfeld
Partner Ambassador
Partner Ambassador

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

lblumenfeld
Partner Ambassador
Partner Ambassador

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

View solution in original post

marcus_sommer

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
albert_guito
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+
lblumenfeld
Partner Ambassador
Partner Ambassador

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;

 

 

lblumenfeld
Partner Ambassador
Partner Ambassador

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;
lblumenfeld
Partner Ambassador
Partner Ambassador

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;
lblumenfeld
Partner Ambassador
Partner Ambassador

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;
lblumenfeld
Partner Ambassador
Partner Ambassador

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

marcus_sommer

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

swarup_malli
Specialist
Specialist
Author

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

swarup_malli
Specialist
Specialist
Author

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