Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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;
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
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!