Qlik Community

Qlik Sense App Development

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

swarup_malli
Valued Contributor

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
lblumenfeld
Contributor II

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:
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
Contributor II

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:
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
Contributor II

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.

View solution in original post

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

View solution in original post

12 Replies
albert_guito
Contributor 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+
lblumenfeld
Contributor II

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:
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
Contributor II

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:
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
Contributor II

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:
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
Contributor II

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:
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
Contributor II

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.

View solution in original post

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

View solution in original post

swarup_malli
Valued Contributor

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

swarup_malli
Valued Contributor

Re: Auto generate fixed nbr of columns and rows?

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