Generic Table Conversion

Previous | Next | View all files | View Slideshow
posted by Ismail Baygın
to, feb 4 2010
Downloads: 147
File size: 125,5kB
Views: 3 627
Generic Table Conversion

This application demonstrates how to convert a generic database table in which the field names are

stored as field values in one column, while the field values are stored in a second into a single

column-based table. It is acting just like the opposite of Crosstable load function 

or one step forward of a generic load.

 

For more information about generic databases,

please see Qlikview Reference Manual pg.475

Comments

Jeanne Petersen wrote re: Generic Table Conversion
on to, feb 4 2010 4:39

Hi there. Just read your code. This is a useful loop that can be used to read dimension tables in. Thank you.

Artjoms Tukums wrote re: Generic Table Conversion
on to, feb 4 2010 6:25

Hi,

Good tool, but what if this part is in the middle of script, when other tables are created?

I found solution for joining tables after generic load by adding..

If Index('$(t)','INL')=1 then

..in outer join loop

But what about dropping unnecessary tables? In your loop all tables, excluding last are dropped. How to drop all tables what names begins with 'INT'?

Rds,

Artjoms

Ismail Baygın wrote re: Generic Table Conversion
on to, feb 4 2010 7:36

Hi Artjoms,

I actually aimed to use this app as a seperate document, not for combining all other table load scripts with it. Or you must use this script always as a first tab in the script editor. But your point is completily right: We need to avoid unrelated tables to be joined or to be dropped.

I suggest a solution to drop only unnecessary tables like below:

let s = NoOfTables();

for i = 0 to $(s)

let t = TableName(0);

If Index('$(t)','INT')=1 then

drop table $(t);

end if

next

Ismail

Artjoms Tukums wrote re: Generic Table Conversion
on to, feb 4 2010 10:18

Actualy we can't use TableName(0) in drop table loop, 'cause we could have some tables created before.

Finaly I found one of possible solutions for this case:

1. we need to catch nomber of tables before and after generic load (using variables, i.e. start and end)

2. we can use a feature, that generated tables after generic load goes one after another. So drop table section will look like..

for i=1 to $(end)-$(start)

let tt = TableName($(start));

If Index('$(tt)','INL')=1 then  //just in case, not obligatory

drop table [$(tt)];

end if

next

Now, Ismail, we can use your great tool in any part of our own script

Rds,

Artjoms

Ralf Becher wrote re: Generic Table Conversion
on to, feb 4 2010 10:24

Hi Ismail,

why don't use:

[code]

Final:

generic load * resident Original;[/code]

There is no need for a loop.

- Ralf

Ismail Baygın wrote re: Generic Table Conversion
on to, feb 4 2010 1:45

Hi Ralf,

Using only Generic load is a kind of messy way.

It generates a lot of tables.

Loop combines the result in one single table.

Functioning is the same of course.

Ismail Baygın wrote re: Generic Table Conversion
on to, feb 4 2010 1:48

Dear Artjoms, many thanks for the improvement.

Ralf Becher wrote re: Generic Table Conversion
on to, feb 4 2010 1:57

Hi Ismail,

you're right but why you want all in one table? It's QlikView and not a relational database ;-)

All analysis will take place...

- Ralf

Ismail Baygın wrote re: Generic Table Conversion
on fr, feb 5 2010 4:43

Absolutely Ralf,

But what if a transformation or a reusage is needed on the generic table in the future. All-in-One table is good, flexible, and reusable I think.

best regards.

Ismail

Paul Yeo wrote re: Generic Table Conversion
on lö, feb 6 2010 12:53

Hi Ismail

Nice work very powerful. may i know if i have a P&L table first column is all variable and second till last column is month_year.

may i know it your code suitable to transform the org P&L table to month_year column instead of value label into a variable ?

Paul

Paul

Feedback Form