topic Script to Generate Tables from template in QlikView

Script to Generate Tables from template

manojkulkarni — Thu, 04 Feb 2016 08:16:03 GMT

Hi All,

I am looking for script from which we can generate tables & columns skeleton using provided excel. Excel will have schema of table.

Below is the sample data present in excel. At the end of load, i should have 3 tables in my QVW. Please share if anybody had script to do the same or any suggestions also welcome.

TableName	Columns
Table1	Col1
	Col2
	Col3
	Col4
Table2	Col21
	Col22
	Col23
	Col24
Table3	Col31
	Col32
	Col33

Re: Script to Generate Tables from template

avinashelite — Thu, 04 Feb 2016 08:19:26 GMT

could you please share the sample data format along with the output format so that we can help you out with the script to generate the data

Re: Script to Generate Tables from template

its_anandrjs — Thu, 04 Feb 2016 08:20:50 GMT

Please provide your sample file.

Regards

Anand

Re: Script to Generate Tables from template

manojkulkarni — Thu, 04 Feb 2016 08:51:00 GMT

input would be below data in excel and expected output will be 3 tables get created in my qlikview application.

TableName	Columns
Table1	Col1
	Col2
	Col3
	Col4
Table2	Col21
	Col22
	Col23
	Col24
Table3	Col31
	Col32
	Col33

Re: Script to Generate Tables from template

sebastianlettner — Thu, 04 Feb 2016 09:37:55 GMT

Hi,

is that what you are looking for?

ExcelTables:
LOAD
    RowNo() as row,
    TableName,
    Columns
FROM
[..\..\..\Desktop\Demo.xlsx]
(ooxml, embedded labels, table is Tabelle1);



TableBase:
LOAD
    if(len(trim(TableName)) = 0, Peek('TableName'), TableName) as TableName,
    Columns
Resident ExcelTables
Order By row asc;

drop table ExcelTables;

LoadColumns:
LOAD
    TableName,
    Concat(Columns, ',') as ColumnsString
Resident TableBase
Group By TableName;

drop table TableBase;

For i=0 to NoOfRows('LoadColumns')

    let _TN = Peek('TableName', i, 'LoadColumns');
    let _COLS = Peek('ColumnsString', 1, 'LoadColumns');
    
    $(_TN):
    LOAD
        $(_COLS)
    FROM $(_TN).qvd (qvd)   
    Resident $(_TN)                      
    ;SQL SELECT $(_COLS) FROM $(_TN);   
    //WhatEverYouNeed  
    
NEXT;

Let _TN = NULL();
Let _COLS = NULL();

Regards

Sebastian Lettner