Getting load to work with if and exists?

[hunnu]

Hey folks.

I have a situation where I need to join a bunch of similar, but not identical, report files into a single.QVD. I've been renaming headers with python before loading but now my boss wants me to get rid of the python step and do it all in Qlik.

The issue is that it's a bunch of .CSV files with slightly different header names. For example "Customer #" vs "Customer number" vs "Client No." There's no option to get these to come in harmonized because they're from different vendors.

I was trying to do something like: Load Distinct If(exists('Customer #'), 'Customer #') as CustomerNumber If(exists('Client No.'), 'Client No.') as CustomerNumber

But I get an error about the headers not being unique even though I'd expect the If() to return nothing when the Exists() condition fails.

Moving the "as CustomerNumber" inside the If() statement is a syntax error.

Is there a way to do this without making temporary tables and conctenating? I'm now doing it this way and it's working but it seems inelegant and like it's more steps than should be needed.

Thanks!

[igoralcantara]

You can use the Rename Mapping function. Check the documentation about that and see if this helps.

Rename field | Qlik Cloud Help

[marksouzacosta]

Hi @hunnu,

I think you can use the ALIAS statement at the beginning of your code, so you can set the alias of all the variations of your field names in one single place:
Alias | Qlik Sense on Windows Help

Rename fields using Mappings - Rename field | Qlik Sense on Windows Help - may be helpful as well - but has a limited scenario. In your case Alias is more appropriate.

[rwunderlich]

Here is a blog post demonstrating using ALIAS to deal with varying column names. 

https://qlikviewcookbook.com/2018/12/loading-varying-column-names/

-Rob