Generic Table Conversion

Previous | Next | View all files | View Slideshow
posted by Ismail Baygın
Thu, Feb 4 2010
Downloads: 349
File size: 125.5kB
Views: 26,673
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 Thu, Feb 4 2010 4:39 AM

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 Thu, Feb 4 2010 6:25 AM

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 Thu, Feb 4 2010 7:36 AM

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 Thu, Feb 4 2010 10:18 AM

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 Thu, Feb 4 2010 10:24 AM

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 Thu, Feb 4 2010 1:45 PM

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 Thu, Feb 4 2010 1:48 PM

Dear Artjoms, many thanks for the improvement.

Ralf Becher wrote re: Generic Table Conversion
on Thu, Feb 4 2010 1:57 PM

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 Fri, Feb 5 2010 4:43 AM

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 Sat, Feb 6 2010 12:53 AM

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

Felix Hummel wrote re: Generic Table Conversion
on Tue, Jun 22 2010 1:34 PM

Dear Ismail,

I saw you made some improvements on your script so that you can use it anywhere in a script.

It'd be great if you could provide the final script with the discussed changes to see how it works. I've put it together myselve but it didn't work.

thanks in advance.

Felix

Ismail Baygın wrote re: Generic Table Conversion
on Mon, Jul 12 2010 10:47 AM

Hi Felix, Sorry for the late reply,

I was unable to take some time to review the application and get back to you.

With the help of Artjoms's addition to my script, I refined it and upload it again as a version 2. So that, from now, you can paste the generic table script in any place in your script,

I hope it may still help you.

Sincerely,

Ismail

Media » Share QlikViews » How-To » Generic Table Conversion
Share
Feedback Form