Re: How I can replicate de ID from different sheet... - Qlik Community

Report Inappropriate Content · ‎2016-07-01

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.

remi_roland · ‎2016-07-01

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.

Report Inappropriate Content · ‎2016-07-04

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.

remi_roland · ‎2016-07-04

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.

Report Inappropriate Content · ‎2016-07-04

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!

remi_roland · ‎2016-07-05

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.