topic Re: Load Redundand Fields in QlikView

Load Redundand Fields

Mon, 28 Oct 2013 16:21:22 GMT

Hi Togehter,

following excellist list is given:

Columns C to L are repeated in every each record.

How can I get the repeating columns in the following Table transformation ?

In this example, the first data set is transformed into 5 records.

The second original data is transformed into 4 records because the redundant fields K and L contains nothing.

The third data set produced only 3 records etc..

How can I load on the first table from Excel, to derive the second Table from the first ?

Thanks for your advice.

Best Regards,

Re: Load Redundand Fields

CELAMBARASAN — Mon, 28 Oct 2013 16:58:23 GMT

Could you post the script formed by the qlikview by choosing the file using Qlikview table files button?

Re: Load Redundand Fields

jaimeaguilar — Mon, 28 Oct 2013 18:24:52 GMT

Hi,

try this

Redundant:

LOAD site_id,

site_name,

category_id,

categorie_name

FROM

RedundantFields.xlsx

(ooxml, embedded labels, table is Sheet1);

for i=1 to 4 //You need to load category_id and categorie_name 4 times besides the one that is already loaded

Concatenate(Redundant)

LOAD site_id,

site_name,

category_id$(i) as category_id,

categorie_name$(i) as categorie_name

FROM

RedundantFields.xlsx

(ooxml, embedded labels, table is Sheet1);

Also take a look at the attached example,

regards

Re: Load Redundand Fields

CELAMBARASAN — Mon, 28 Oct 2013 18:40:36 GMT

Hi,

Could you possibly try this

Data:

LOAD site_id,

site_name,

category_id & '|' & category_id1 & '|' & category_id2 & '|' & category_id3 & '|' & category_id4 as category_id,

categorie_name & '|' & categorie_name1 & '|' & categorie_name2 & '|' & categorie_name3 & '|' & categorie_name4 as categorie_name

FROM

RedundantFields.xlsx

(ooxml, embedded labels, table is Sheet1);

Result:

LOAD

RowNo() AS UniqueNumber,

site_id,

site_name,

SubField(category_id, '|') AS category_id

Resident Data;

Left Join(Result)

LOAD

RowNo() AS UniqueNumber,

site_id,

site_name,

SubField(categorie_name, '|') AS categorie_name

Resident Data;

DROP Table Data;

Re: Load Redundand Fields

Tue, 29 Oct 2013 14:48:36 GMT

Hi Jaime,

thank you very much for your help. The Post from Celambarasan did solve my problem.

Best Regards,