Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
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
MVP & Luminary
MVP & Luminary

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;

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
MVP & Luminary
MVP & Luminary

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

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!