For Each - how to add 'Title.' before every field name except the field called 'ID'

[nickmarlborough]

Hi, 

Have a table:

ID

Name

Country

i would ideally like it as:

ID

Title.Name

Title.Country

without having to add manually in case new columns appear. Most important is that ID doesnt get the 'Title.' in front of it.

[Chanty4u]

Try this

MappingTable:

Mapping

Load * Inline [

OriginalFieldName, TitleFieldName

Name, Title.Name

Country, Title.Country];

MainTable:

Load

ID,

Name,

Country

From ...

RenamedMainTable:

Load

ID,

ApplyMap('MappingTable', 'ID', 'ID') as ID,

ApplyMap('MappingTable', 'Name', 'Name') as Title.Name,

ApplyMap('MappingTable', 'Country', 'Country') as Title.Country

Resident MainTable;

[Oleg_Troyansky]

Hi Nick,

Look into commands QUALIFY and UNQUALIFY - that's the answer to your question.

Having said that, think about the future uses of these fields in your visualizations. If any of these fields will appear in charts as dimensions, will you want them to appear in this "user unfriendly" way? For example, in a chart by Country, will you want to see:

Country.Name vs. Country or Country Name ?

Based on your future needs, you may choose to actually rename the fields manually and give them more user friendly names than those you could get automatically.

Cheers,

[nickmarlborough]

the issue I have is.... I have 2 tables (table 1 and table 2)

Table1 fields: ID, Name, Country

Table2 fields: ID, Name, City

I would like to associate the two tables ONLY on ID and make sure other common fields like Name are not associated and therefore adopt names such as T1.Name and T2.Name.

This is where the QUALIFY and UN-QUALIFY become challenging, as I can't qualify one field in one table and unqualify the rest and have them belong to the same table Table1...(hope this makes sense)

if I can how would I write this in the load script? THANKS!

[pravinboniface]

I feel that the Qualify and Unqualify suggested above should work-  Here's an example you can try and then check the data model viewer.

Qualify *;
Unqualify ID;
Table1:
Load * inline [
ID,Name,Country
1,Me,Finland
];

Table2:
Load * inline [
ID,Name,Country
2,You,Norway
];
exit Script;

[steeefan]

A common approach in Qlik development is to prefix your key fields with %, so you could also write it like this:

QUALIFY *;
UNQUALIFY '%*';

T1:
NOCONCATENATE LOAD
  ID AS %ID,
  Name,
  Country
FROM
  ...;

T2:
NOCONCATENATE LOAD
  ID AS %ID,
  Name,
  Country
FROM
  ...;

UNQUALIFY *;

[marcus_sommer]

Using any (manually or automatically) qualifying approach to prevent unwanted associations is (nearly always) a painful detour. Better is to follow the officially recommendations and using a star-scheme as data-model which means having a single fact-table with n dimension-tables by de-normalizing the data as much as possible respectively in your case to merge both of these tables (by joining/mapping and/or concatenate).

It's much easier as it may look like and needs less efforts and knowledge as applying any other kind of data-model.