topic Re: Modifying fields in LOAD without specifying all of them in QlikView

Modifying fields in LOAD without specifying all of them

sir_ogrim — Wed, 25 Nov 2020 16:16:04 GMT

Hi!

I'm rather new to QlikView, and I was hoping some more experienced users might like to give me a pointer.

Consider this:

LOAD
EmployeeID,
LastName,
FirstName,
Date(BirthDate, 'yyyy-MM-dd') as BirthDate,
Date(HireDate, 'yyyy-MM-dd') as HireDate,
LastName & ', ' & FirstName as SortName,
Date(Today()) - HireDate as HireTime;

SQL SELECT
EmployeeID,
LastName,
FirstName,
BirthDate,
HireDate
FROM Northwind.dbo.Employees;

In the above code I don't want to do a SELECT * clause, because I believe that would cause more database load than necessary. I can't do a LOAD * because I don't want to load the all selected fields exactly as-is (I want to modify the date fields). I could of course do these date modifications in the SQL clause, but here's my question:

Is there some "shorthand" way to write a load statement like this:

// Load all fields from select clause as-is

// ... EXCEPT for these two fields, which I would like to modify:
Date(BirthDate, 'yyyy-MM-dd') as BirthDate,
Date(HireDate, 'yyyy-MM-dd') as HireDate,

// ... and then add some new fields.
LastName & ', ' & FirstName as SortName,
Date(Today()) - HireDate as HireTime;

I hope the above pseudo-code makes sense. My aim here is to reduce the amount of code, not having to specify all fields twice just because I need to modify some of them.

Is this possible?

Thanks in advance to anyone who might take pity on a noob.

Re: Modifying fields in LOAD without specifying all of them

Anil_Babu_Samineni — Wed, 27 Sep 2017 07:38:38 GMT

You can do

Load LastName & ', ' & FirstName as SortName,

EmployeeID,

LastName,

FirstName,

Date(BirthDate, 'yyyy-MM-dd') as BirthDate,

Date(HireDate, 'yyyy-MM-dd') as HireDate,

LastName & ', ' & FirstName as SortName,

Date(Today()) - HireDate as HireTime;

Re: Modifying fields in LOAD without specifying all of them

marcus_sommer — Wed, 27 Sep 2017 09:06:30 GMT

In the way which you describe it's not possible.

Of course you could load tables which contain your source-table, field-names and their possible formattings and other transformations and a lot of other informations and creating with them a load-script on the fly - but you will at least transfer a certain part of write-work to another place/tool and more important you would add a lot of complexity. It would be not simplifying things else it would be the opposite ...

- Marcus

Re: Modifying fields in LOAD without specifying all of them

sir_ogrim — Wed, 27 Sep 2017 11:22:35 GMT

Thank you for your reply! Something like this would have been convenient, but on the other hand if I need to rename most fields (which I realise I will probably be doing a lot), the need for a "shorthand" solution might not be so big after all.

Again, thank you!