topic Re: LOAD all fields from RESIDENT in QlikView

LOAD all fields from RESIDENT

sir_ogrim — Tue, 15 Nov 2022 17:54:53 GMT

Hi!

This will most likely be a very easy newbie question, but I haven't been able to successfully Google it.

In this example, I want to load the whole [EXT_Person] table as [Person], but I also need to rename the [Id] field. This example solves the code problem:

Person: LOAD Id AS %Person_Id, * RESIDENT EXT_Person; DROP FIELD Id; DROP TABLE EXT_Person;

However, I would like to load the fields like this, i.e. listing all the fields one by one:

Person: LOAD Id AS %Person_Id, FirstName, LastName, ... etcetera ... RESIDENT EXT_Person; DROP TABLE EXT_Person;

My question is this: In the Qlik Sense Enterprise user interface, is there a way to do this load without having to type in all the field names manually? In my example I only need to rename one field, but having to rename multiple fields and then dropping them one by one is not pretty.

One slightly less cumbersome (but still rather clumsy) way to achieve this woud be to load the table from the Connection dialog once more (which would give me a full SQL SELECT and LOAD script) and then remove the SQL SELECT part. Note that the %Person_Id field is a key (it won't be unique), which (if I understand correctly) rules out the RENAME function.

I wish you all a great day!

Re: LOAD all fields from RESIDENT

Prashant_Naik — Tue, 15 Nov 2022 18:56:14 GMT

Hi,

You can try for Qualify and Unqualify.

qualify *;

unqualify Id;

load *,

id as %Person_Id

resident tablename;

https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularStatements/Qualify.htm

Hope it helps!

Re: LOAD all fields from RESIDENT

sir_ogrim — Mon, 21 Nov 2022 07:31:52 GMT

Hi! I'm afraid that doesn't solve the problem, since it requres unqualifying of each field to rename rather than dropping. What I'm looking for here is something like right-clicking on a table in SQL Server Management Studio and choosing "Select Top 1000 Rows" to get a script that I can easily modify. Something like "press CTRL-something to list all fields of a table". In other words, it's more of a GUI than a scripting question.

Thank you for you response!

Re: LOAD all fields from RESIDENT

marcus_sommer — Mon, 21 Nov 2022 09:18:39 GMT

An alternatively to your approach might be the ALIAS feature which moved the rename-logic before the load.

Beside this method and other ways of renaming - with/without noconcatenate, qualifying, map using ... - none would list the fields. To get this you could use the load-wizard on the origin load - or sometimes better the first load was stored within a qvd and now you are loading per wizard from the qvd.

Personally, I wouldn't do this else doing it in beforehand - a resident-load which is just renaming a field is IMO superfluous ...

- Marcus

Re: LOAD all fields from RESIDENT

sir_ogrim — Mon, 21 Nov 2022 09:33:06 GMT

Hi,

Ah, I many have been a bit unclear in my description of the problem. I do intend to transform some of the data, and rename some fields. All the loading has been done previously by looping through a bunch of tables and saving them as QVD.

But the Alias method will be quite useful. Tank you!

HOWEVER... I was not aware I could use the wizard to load a QVD. Don't ask me how I managed to overlook this... That perfectly solves my problem.

So... You solved this noob's problem and managed to teach him a bit more besides that.

Many thanks!