Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sir_ogrim
Contributor II
Contributor II

Modifying fields in LOAD without specifying all of them

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.  

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

3 Replies
Anil_Babu_Samineni

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
marcus_sommer

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

sir_ogrim
Contributor II
Contributor II
Author

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!