topic Re: Dynamic loading of tables from Data Base in QlikView

Dynamic loading of tables from Data Base

harishicon — Thu, 07 Jul 2016 17:53:58 GMT

Hello All,

I have one excel which is containing the tables name . The Tables name which are mentioned should be picked up from the database and two flags to be added in the table along the fields name . Name of tables can grow in excel.

For Example:- In Excel there is Table name

Maxx_MIS_H where Maxx is Table name, MIS is BU Code and H is Ledger.

Now Maxx should be picked up and MIS and H will be add as column in form of flag along with table's fields.

Please guide me how can I pick the table name form excel and connect with the data base as well as how can i make flag in the table as mentioned above dynamically.

Thanks in advance....

Re: Dynamic loading of tables from Data Base

boorgura — Thu, 07 Jul 2016 18:39:37 GMT

If it is one value in excel:

you can capture the values in variables.

ExcelRead:

LOAD Value

frOM <excelfile> ;

LET vValue = peek('Value',0, 'ExcelRead');

LET vTable = subfield(vValue, '_', 1)

LET vBU = subfield(vValue, '_', 2)

LET vLedger = subfield(vValue, '_', 3)

LOAD *,

'$(vBU)' as BU,

'$(vLedger)' as Ledger;

SQL SELECT *

FROM '$(vTable)'

In case of multiple table names from excel file - you will have loop thru the above code based on the number of records within the excel

Re: Dynamic loading of tables from Data Base

harishicon — Fri, 08 Jul 2016 11:04:39 GMT

how to use loop in above scenario. Please tell me

Re: Dynamic loading of tables from Data Base

boorgura — Fri, 08 Jul 2016 12:21:40 GMT

For looping, you can use the same script enclosed in a loop.

ExcelRead:

LOAD Value

frOM <excelfile> ;

LET vRowCount = NoOfRows('ExcelRead');

FOR i = 0 to $(vRowCount)

LET vValue = peek('Value',$(i), 'ExcelRead');

LET vTable = subfield(vValue, '_', 1)

LET vBU = subfield(vValue, '_', 2)

LET vLedger = subfield(vValue, '_', 3)

LOAD *,

'$(vBU)' as BU,

'$(vLedger)' as Ledger;

SQL SELECT *

FROM '$(vTable)'

NEXT

if you were to concatenate all the DB tables into one table - you will have to declare the table name before the loop with null values. And then use a Concatenate(TableName) before the LOAD statement in the loop.

Re: Dynamic loading of tables from Data Base

harishicon — Fri, 08 Jul 2016 15:57:10 GMT

Thanks Dear.

I also created . please have a look

ExcelRead:

LOAD Value

frOM <excelfile> ;

FOR Each a in FieldValueList('Value')

LET vValue = peek('Value',$(a), 'ExcelRead');

LET vTable = subfield(vValue, '_', 1)

LET vBU = subfield(vValue, '_', 2)

LET vLedger = subfield(vValue, '_', 3)

LOAD *,

'$(vBU)' as BU,

'$(vLedger)' as Ledger;

SQL SELECT *

FROM '$(vTable)'

Next a;