Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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?

debito.png

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.

7 Replies
remi_roland
New Contributor III

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?

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.

Not applicable

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?

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
New Contributor III

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?

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.

Not applicable

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?

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
New Contributor III

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?

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.

Not applicable

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?

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.

qliksus
Valued Contributor

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?

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?