Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jblomqvist
Valued Contributor

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

Hi all,

Let's say I have a table like this:

FieldNameDataFormatDependency
OrderIDNumericNone
OrderDateDatetimeNone
CustomerIDStringNone
CustomerNameStringNone
DispatchDateDateOrderDate

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:

OrderIDOrderDateCustomerIDCustomerNameDispatchDate
9 Replies
miguelbraga
Valued Contributor III

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

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,

MB

jblomqvist
Valued Contributor

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

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.

MVP & Luminary
MVP & Luminary

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

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

filip_duchateau
Contributor II

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

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?

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)]';

NEXT

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?

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

MVP
MVP

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

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.

MVP & Luminary
MVP & Luminary

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

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?

Hi,

another solution could be:

QlikCommunity_Thread_218093_Pic1.JPG

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