Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
alec1982
Specialist II
Specialist II

Load of table with the fields names being dynamic

I have a table where the some of the headers are not the same. the table has following columns (Name, Category and then several columns that start with abc (for example abc12, abc34, abc56). I want to pull in the name, category and then the abc column with the highest number (in our sample here, it will be abc56).

any idea how to do that?

1 Solution

Accepted Solutions
fosuzuki
Partner
Partner

So I am assuming that the number of columns and their names can vary. Try this:

Data:
CrossTable(Type, Value, 2)
LOAD
*
FROM [lib://Desktop/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

Max:
load distinct
Type,
replace(Type, 'abc', '') as T
Resident Data;

Inner Join(Max)
load max(T) AS T
Resident Max;

Inner Join(Data)
load Type
Resident Max;

View solution in original post

4 Replies
fosuzuki
Partner
Partner

Hi,

The data is in what kind of source? SQL, xls, in-memory table, txt, ...?

 

alec1982
Specialist II
Specialist II
Author

excel

fosuzuki
Partner
Partner

So I am assuming that the number of columns and their names can vary. Try this:

Data:
CrossTable(Type, Value, 2)
LOAD
*
FROM [lib://Desktop/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

Max:
load distinct
Type,
replace(Type, 'abc', '') as T
Resident Data;

Inner Join(Max)
load max(T) AS T
Resident Max;

Inner Join(Data)
load Type
Resident Max;

alec1982
Specialist II
Specialist II
Author

close to what I needed. I adjust it to address the order of the columns as this is another issue where the first and second fields are not always the same.