Rename columns with same name in Excel while loading it all (*) and making an incremental Load?

[DiegoF]

Hi there folks!

So I know that when you load an .xlsx and have two columns with the same name (Currency, Currency), Qlikview will automatically put a number at the end of the dimension so he can differentiate each column and its values. Now then, I would like to LOAD * from the .xlsx so if they add new columns with the same name (that it will happen), the load script gets the new info and becomes like an incremental LOAD.

Now, because I´m trying to autoincrement this LOAD with *, I can´t play with the dimensions names and I would like that the same column names stay like that, so they "group by" that same name dimension, but Qlik makes that number thing so he can read and load it correctly. Is it even possible to do it the way I´m trying to do it? Or should I use a loop and try to play around with it (The structure of the excel is, that the name columns repeat after 3 columns, so like: Name, Subject, Date, Name, Subject, Date ... and so on). My goal is to automatize it a lot, so I would like to do it in one load, no Concatenates because if there is a new column I guess I had to add it manually.

Thanks!

[marcus_sommer]

I assume it's not possible with a single load. Within the table-wizard are various transformation-features (not just pressing finish else go to the further-button) which if I remember correctly could handle repeating headers - whereby I'm not sure if it could handle an unknown number of fields or just those which were present by creating this script. AFAIK the documentation to these features are very small and I don't use them - but there are possibilities.

I think I would do a pre-load of the Excel and counting the number of fields and then looping over them by loading the fields without labels and renaming them directly, like:

Excel: first 1 load * from Excel;

for i = 1 to nooffields('Excel') / 3
   t: load A as Name, B as ... from Excel;
next

whereby the field-alias like A,B,C would need some kind of resolution (might be done within another loop).

If I think again I would at first check how the Excel data look like if they would be loaded with an ODBC driver ... Maybe you could save some efforts.

- Marcus

[DiegoF]

Hi,

Thanks for the advice! I'm trying to do it somehow like you said, but I don´t know why my code is not working. I´m trying to use the @1, @2 ... and so on to read and load the columns of the table,  it tells me that @1, @2 ... @10 exists, but doesn´t get any values, so the loop looks like it works. Am I doing something wrong? Heres the code:

And here I see that the loop is working, but not getting me the values (I think):

[marcus_sommer · Accepted solution]

Your source is within the newer file-format xlsx and there the fields aren't fetched as @1 ... @n else with the column-letters from Excel like A, B, C. This means you need a translation-logig, maybe with something like:

for i = 1 nooffields('Excel') / 3
   let F1 = chr(65 + ($(i) - 1) * 3);
   let F2 = chr(66 + ($(i) - 1) * 3);
   let F3 = chr(67 + ($(i) - 1) * 3);
   t: load $(F1) as x, $(F2) as y, $(F3) as z resident Excel;
next

It's just written down and therefore the logic + syntax may not flawlessly but the general approach should be working.

- Marcus

[DiegoF]

Yes! I got it with this code, also I'm still new with Qlik so I didn't knew that Chr(65) was for example letter A, but thanks to you I've learned something very useful and I can apply in future Excel reads. Altho this is the solution, I have two questions about the code. Why do you have to do the /3 in NoOfFields and why in each variable you do the i-1 and *3? I'm guessing it´s so the index i of the loop for gets its values right (from 1,2,3 ... to 10 in this case) but I would like if you could explain more in detail so I can understand fully if you don't mind.

Also, the other question is that I had to take out the @B, @C in the load, because yeah, the columns on Excel are named B, C ... and not with the  @, but when could you use it like @1,@2 ... I think I´ve seen it in other posts. So when can you use it like that.

Thank you so much!

- Diego

[marcus_sommer]

The function chr() return just the letter/sign to a numeric index - it's a very old logic. Here a starting point for it:

ASCII - Wikipedia

The / 3 is used because you have groups of 3 fields and here it's essential to count the number of groups and not all fields - whereby other logics for the counting + fetching/mapping/assigning the field-names behind it are probably possible.

The @1 ... logic for loads with no labels are applied by the older xls file-format and txt/csv-files because there don't exists a valid information about the columns and therefore they are just counted but a xlsx is a xml-file and has such information and therefore the origin A, B, ... names are used.

- Marcus