Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Solving the Informatica Dilemma: On-Demand Briefing - Watch On Demand!
cancel
Showing results for 
Search instead for 
Did you mean: 
NikVladi
Contributor II
Contributor II

Get unique values ​​in a field in a model in a script

For example, I have 1000 tables in the model with the field Field1 and I want to get unique values ​​from this field from all tables (from the model).
For example, I will need to write these values ​​to the qvd table.
How can I do this without accessing each table?

Labels (1)
1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Something like below...

Sales:
Load * Inline
[
Country, SALED
    UK, 100
    USA, 200
    INDIA, 150
    FRANCE, 400
    CANADA, 125
];
 
Country:
Load * Inline
[
Country, CONTINENT
    UK, EUROPE
    FRANCE, EUROPE
    USA, NORTH AMERICA
    CANADA, NORTH AMERICA
    INDIA, ASIA
    CHINA, ASIA
AUSTRALIA, OCENIA
];
 
Customer:
Load * Inline
[
Customer, Country
    C1, INDIA
    C2, UK
    C3, INDIA
    C4, USA
    C5, CHINA
    C6, CANADA
    C7, NEW ZEALAND
];
 
For Each vCountry in FieldValueList('Country')
 
Trace $(vCountry);
COUNTRY:
Load 
'$(vCountry)' as Country 
Autogenerate 1;
 
Next vCountry
 
Store COUNTRY INTO [$(vLocation)/COUNTRY_FIELD.qvd](QVD);
Drop Table COUNTRY;
 
Drop Table Sales;
Drop Table Customer;
Drop Table Country;
 
 

View solution in original post

2 Replies
MK_QSL
MVP
MVP

Something like below...

Sales:
Load * Inline
[
Country, SALED
    UK, 100
    USA, 200
    INDIA, 150
    FRANCE, 400
    CANADA, 125
];
 
Country:
Load * Inline
[
Country, CONTINENT
    UK, EUROPE
    FRANCE, EUROPE
    USA, NORTH AMERICA
    CANADA, NORTH AMERICA
    INDIA, ASIA
    CHINA, ASIA
AUSTRALIA, OCENIA
];
 
Customer:
Load * Inline
[
Customer, Country
    C1, INDIA
    C2, UK
    C3, INDIA
    C4, USA
    C5, CHINA
    C6, CANADA
    C7, NEW ZEALAND
];
 
For Each vCountry in FieldValueList('Country')
 
Trace $(vCountry);
COUNTRY:
Load 
'$(vCountry)' as Country 
Autogenerate 1;
 
Next vCountry
 
Store COUNTRY INTO [$(vLocation)/COUNTRY_FIELD.qvd](QVD);
Drop Table COUNTRY;
 
Drop Table Sales;
Drop Table Customer;
Drop Table Country;
 
 
seanbruton
Partner - Creator II
Partner - Creator II

Kudos nice simple solution!!