topic Re: How I can replicate de ID from different sheets to then use left join and fill the values of other columns that are missing with NULLASVALUE? in QlikView

How I can replicate de ID from different sheets to then use left join and fill the values of other columns that are missing with NULLASVALUE?

Fri, 01 Jul 2016 13:54:32 GMT

I need to replicate in the table every Rut (ID) that exists in every sheet so in each year (AÑOMES) will be every ID to then use left Join and fill with nullasvalue.

Re: How I can replicate de ID from different sheets to then use left join and fill the values of other columns that are missing with NULLASVALUE?

remi_roland — Fri, 01 Jul 2016 19:33:33 GMT

Hi,

I not sure to understand what you want to do.

Can you provide a sample data set ?

Is it the sheets from an unique excel file you want to merge?

Regards.

Rémi.

Re: How I can replicate de ID from different sheets to then use left join and fill the values of other columns that are missing with NULLASVALUE?

Mon, 04 Jul 2016 14:47:51 GMT

Hi,

Remy, the data is from different files of excel that use the same name and are read with *, but I understand that it can be useful the function Distinct, right? Do you need the script or the excel file to? Thanks.

Re: How I can replicate de ID from different sheets to then use left join and fill the values of other columns that are missing with NULLASVALUE?

remi_roland — Mon, 04 Jul 2016 16:01:51 GMT

Hi Fernando,

If i understand your issue, you have ID's in excel sheets.

Every excel is for a specific year and ID's can exist in multiples years, right?

And you issue is to figure from which year the ID belongs?

If it's that, you can add a new field based on the year in filename to your Load statement.

Exemple: If your excel file is named like this : "clasificacion 2016.xlsx"

LOAD
text(RUT) as Rut,
left(right(filename(),9),4) as Year
FROM clasificacion 2016.xlsx (....);

Hope it will help you.

Regards,

Rémi.

Re: How I can replicate de ID from different sheets to then use left join and fill the values of other columns that are missing with NULLASVALUE?

Mon, 04 Jul 2016 20:22:06 GMT

Hi Remi,

Thank you, but I need to create a data of all the ID's existing in the sheets so It doesn't matter in how many periods the ID exists because I need the ID´s to do left join with the information of the clients. The next tables include a column with the Year-Month, so it will replicate the ID for every period. I think the problem it can be solve using distinct and saving a QVW with all the information.

RUTERO:

LOAD Distinct text(Rut) as Rut

// AÑOMES,

// [Tipo Persona],

// Cliente,

// Nombre,

// Sucursal,

// Ejecutivo,

// clasificacion

FROM

(ooxml, embedded labels, table is Sheet1) where [Tipo Persona] = 'PN';

STORE RUTERO INTO (QVD);

correct me if I'm wrong, thank you very much!

Re: How I can replicate de ID from different sheets to then use left join and fill the values of other columns that are missing with NULLASVALUE?

remi_roland — Tue, 05 Jul 2016 07:03:58 GMT

Hi,

OK I finally get it. You have 1 row in classification that match multiple row in clients table.

So even if you perform a distinct in classification, you will still have many rows as long as you do a left join on client table.

I suggest to perform a distinct to avoid lines duplications in classification and load your client table séparately.

Don't do any join just load the table and let qlikview perform the link on "RUT" column.

If it still not solve your issue, please post a sample of data and I'll have a look at it.

Regards.

Remi.

Re: How I can replicate de ID from different sheets to then use left join and fill the values of other columns that are missing with NULLASVALUE?

Wed, 06 Jul 2016 14:58:49 GMT

Finally I use Distinct and store all the data in a QVD to then left joining the columns I need.

RUTERO:

LOAD Distinct text(Rut) as Rut

// AÑOMES,

// [Tipo Persona],

// Cliente,

// Nombre,

// Sucursal,

// Ejecutivo,

// clasificacion

FROM

(ooxml, embedded labels, table is Sheet1) where [Tipo Persona] = 'PN';

STORE RUTERO INTO (QVD);

Thanks Remi.

Re: How I can replicate de ID from different sheets to then use left join and fill the values of other columns that are missing with NULLASVALUE?

qliksus — Thu, 07 Jul 2016 09:04:15 GMT

Do you want to fill all the ID for all the years ?. I mean let say if 2014 has 10 ID's and 2015 has 20 IDS do you want a output of 20 ID's in both 2014,2015?