topic Re: Interesting challenge: How to create a real table from a list of field names? in QlikView

Interesting challenge: How to create a real table from a list of field names?

jblomqvist — Tue, 24 May 2016 11:41:49 GMT

Hi all,

Let's say I have a table like this:

FieldName	DataFormat	Dependency
OrderID	Numeric	None
OrderDate	Datetime	None
CustomerID	String	None
CustomerName	String	None
DispatchDate	Date	OrderDate

I would like to load the table above, and then create an actual 'real' table with fields from the FieldName column.

The above table need can have any given number of fields. So for example at the moment it's 5, but tomorrow a user might add 3 more so it would be 8 fields.

The 'real' table from above needs to reflect every field from the above table.

Any idea how to do that please?

The end result from the above example would be something like this:

OrderID	OrderDate	CustomerID	CustomerName	DispatchDate

Re: Interesting challenge: How to create a real table from a list of field names?

miguelbraga — Tue, 24 May 2016 11:46:12 GMT

Hey there John

I think you can export your structure to a file and use it in your dashboard like this:

Hope it helps

Regards,

Re: Interesting challenge: How to create a real table from a list of field names?

jblomqvist — Tue, 24 May 2016 12:12:16 GMT

Hi Miguel,

Thanks, however I am not looking to export my data, I am looking to import the fields from a file and create the table in QlikView.

Re: Interesting challenge: How to create a real table from a list of field names?

marcus_sommer — Tue, 24 May 2016 12:27:54 GMT

You could create a load-statement on the fly with something like this:

LoadStatement:

load concat(Fieldname, ', ') as FieldsForLoadStatement From Yourtable;

let vFieldsForLoadStatement = peek('FieldsForLoadStatement', 0, 'LoadStatement');

FinalTable:

Load $(vFieldsForLoadStatement) From XYZ;

I think you will need a bit more to make such approach really useful like the including of the TableNames, data-sorces and some informations for the to applying formatings (for example, beside the format-function like num/date ... also the format-string like '#.##0,0' or 'MM/DD/YYYY') - and of course now will it be a bit more complex to combine these.

- Marcus

Re: Interesting challenge: How to create a real table from a list of field names?

Anonymous — Tue, 24 May 2016 12:38:46 GMT

Hi John,

Have you tried using this ?

I have created an Excel file with your table and used 'Rotate' -> 'Transpose' to get the data in QlikView.

Here's the Excel - and qvw file.

Re: Interesting challenge: How to create a real table from a list of field names?

Peter_Cammaert — Tue, 24 May 2016 12:44:13 GMT

Two remarks with respect to your request:

AFAIK it's difficult to create a table in QlikView that hasn't got a single row. AUTOGENERATE 0 will help you
QlikView manages field types dynamically and cell-by-cell. QV is not some sort of RDBMS where you assign a single data type to a entire column. And without even a single row of data, there isn't even a cell type to speak about as you will only have headers. The cell characteristics can be manipulated as soon as you load real values.

Although Marcus' example is way more elegant, this one shows some other techniques

ColumnTable:

LOAD * INLINE [

FieldName, DataFormat, Dependency

OrderID, Numeric, None

OrderDate, Datetime, None

CustomerID, String, None

CustomerName, String, None

DispatchDate, Date, OrderDate

];

LET vColumnList = '';

FOR i = 1 TO FieldValueCount('FieldName')

LET vFieldName = FieldValue('FieldName', i);

IF (i > 1) THEN

LET vColumnList = vColumnList & ', ';

END IF

LET vColumnList = vColumnList & '0 AS [$(vFieldName)]';

NewTable:

LOAD $(vColumnList)

AUTOGENERATE 0;

Please do add the square bracket stuff. As soon as you store a field name with spaces in your table definition file, the earlier script will break down. This one will not.

Best,

Peter

Re: Interesting challenge: How to create a real table from a list of field names?

Peter_Cammaert — Tue, 24 May 2016 12:52:32 GMT

After taking a lesson from Mr; Sommer

ColumnTable:

LOAD * INLINE [

FieldName, DataFormat, Dependency

OrderID, Numeric, None

OrderDate, Datetime, None

CustomerID, String, None

CustomerName, String, None

DispatchDate, Date, OrderDate

];

FieldList:

LOAD Concat('0 AS [' & FieldName & ']', ', ') AS ColumnList RESIDENT ColumnTable;

LET vColumnList = peek('ColumnList');

NewTable:

LOAD $(vColumnList)

AUTOGENERATE 0;

Best,

Peter

Re: Interesting challenge: How to create a real table from a list of field names?

swuehl — Tue, 24 May 2016 13:09:35 GMT

Since your table is just meta data of a real table and you are asking of for a real table, I assume you want to create a (mock up, sample data) table based on the meta data?

maybe like

ColumnTable:

LOAD * INLINE [

FieldName, DataFormat, Dependency

OrderID, Numeric, None

OrderDate, Datetime, None

CustomerID, String, None

CustomerName, String, None

DispatchDate, Date, OrderDate

];

FieldList:

LOAD Concat(

Pick(Match(DataFormat,'Numeric','Datetime','String','Date'),

'Round(100*Rand())',

'Timestamp(Makedate(2016)+Rand()*366)',

'CHR(ORD(''A'')+floor(Rand()*26))',

'Date(Makedate(2016)+floor(Rand()*366))'

) & ' AS [' & FieldName & ']', ', ') AS ColumnList RESIDENT ColumnTable;

LET vColumnList = peek('ColumnList');

NewTable:

LOAD $(vColumnList)

AUTOGENERATE 1000;

Adjust the functions to create your sample data as needed.

Re: Interesting challenge: How to create a real table from a list of field names?

marcus_sommer — Tue, 24 May 2016 13:36:23 GMT

You are right, it needs to be extend to such cases with spaces or special chars within the field/table-names.

- Marcus

Re: Interesting challenge: How to create a real table from a list of field names?

MarcoWedel — Tue, 24 May 2016 22:24:17 GMT

Hi,

another solution could be:

mapFormatValues:
Mapping LOAD * INLINE [
    Format,  Value
    Numeric,  Ceil(Rand()*1000)
    String,  "KeepChar(Hash256(Rand()),'ABCDEFGHIJKLMNOPQRSTUVWXYZ')"
    Datetime, TimeStamp(YearStart(Today())+Rand()*(Now()-YearStart(Today())))
    Date,    Date(YearStart(Today())+Floor(Rand()*DayNumberOfYear(Today())))
];

table1:
Generic
LOAD IterNo() as ID,
    FieldName,
    Evaluate(ApplyMap('mapFormatValues',DataFormat))
FROM [https://community.qlik.com/thread/218093] (html, codepage is 1252, embedded labels, table is @1)
While IterNo() <= 100;

hope this helps

regards

Marco