topic Re: Add new column to a resident table in QlikView

Add new column to a resident table

Wed, 22 Oct 2014 06:52:12 GMT

Hi,

Suppose that in Qlikview load script, I have loaded a table (Reporting) with the following structure

Country	Sale Type	Sale Type alloc
Finland	General
USA		General
UK	Pre-Sales	Pre-Sales
Germany	General
Sweden		General
Holland	General	General

Later in the script, I would like to add another column SaleType All to this table that would be the combination of Sale Type and Sale Type alloc

Can someone please tell me what are the possible options to do this. In my opinion, one way to do it is by

-creating a temp table and load data from From Reporting table where Sale Type is not null union load data from reporting table where sale Type alloc is not null. Then load distinct rows from this temp table and drop the temp table

The above table is just an example but the actual table has 72 columns and 70 million rows. This table is generated dynamically step by step by calling different stored procedures and filling/concatenating data in this table

I would like to implement the most efficient way to do this

P.S: The purpose is to have a single column for filter that would filter both columns for the sale types and also have a single column sale type column appear in a chart/table box

Shah

Re: Add new column to a resident table

jonathandienst — Wed, 22 Oct 2014 07:19:42 GMT

The most efficient way would be to use a preceding load, above the load of the Reporting table:

Reporting:

LOAD *,

If(Len([Sale Type]) > 0, [Sale Type], [Sale Type alloc]) As [SaleType All]

;

LOAD ......

// existing load of Reporting here

FROM ....

;

If that is not an option, you could use a join, but this will add to the reload time for 70 million rows:

Join(Reporting)

LOAD Distinct [Sale Type],

[Sale Type alloc],

If(Len([Sale Type]) > 0, [Sale Type], [Sale Type alloc]) As [SaleType All]

Resident Reporting;

I would prefer the preceding load if at all possible.

HTH

Jonathan

Re: Add new column to a resident table

Wed, 22 Oct 2014 07:28:00 GMT

The first one is not possible or very hard way to do it because the reporting table is created gradually and dynamically by merging many columns from different fact and entity tables

I would go for the second option but there is a small problem with it. Since it will join on both Sale Type and Sale Type alloc and in most of the cases either of them is null, will this join not fail?

Shah

Re: Add new column to a resident table

jonathandienst — Wed, 22 Oct 2014 07:44:11 GMT

I think you are correct. A quick workaround is to place the statement:

NULLASVALUE [Sale Type], [Sale Type alloc];

before the first load of the Reporting table.

HTH

Jonathan

Re: Re: Add new column to a resident table

Wed, 22 Oct 2014 12:13:36 GMT

Yes, it seems to b partially working. However, the nullasvalue seems to have replaced only the nulls that existed in the table. It does not seem to replace those nulls that were created as a result of mismatching joins between tables.

On a side node, Is it possible to do a load from the same table, add few columns or changes and give the table the same name? for example

Reporting:
replace reload *,
          If(Len([Sale Type]) > 0, [Sale Type], [Sale Type alloc]) As [SaleType All]
resident Reporting;

Shah