topic Re: Allocate Field Types to Data Columns in QlikView

Allocate Field Types to Data Columns

Anonymous — Sat, 16 Nov 2024 21:43:06 GMT

Hello,

I am trying to set up my data load so in Qlik Sense I can filter for only data of certain "types".

I have some sample data as shown in the table below with the column headers as shown:

Group ID	Medicare Number	MRN	Patient Area Unique Identifier	First Given Name	Second Given Name	Surname	Alias Surname	Sex	Date of Birth
100124	44137735982	352454	1943146	Trevor		AXEL	AXEL TREVOR	1	5071957
100134	43432965142	660992	1721770	Perisher	Terry	COSBY	COSBY Perisher Terry	1	19111956
100134	43432965142	660992	1721770	Perisher	Terry	COSBY	COSBY Perisher Terry	1	19111956
100120	48189868742	745690	2886650	Tracey	Warren	FRYER		1	24061980
100120	48189868742	745690	2886650	Tracey	Warren	FRYER		1	24061980
100120	48189868742	745690	2886650	Tracey	Warren	FRYER		1	24061980
100014	51336986702	363094	1955234	Laurence		NEWTOWN	NEWTOWN LAURIE Terry	1	4061960

I then wish to be able to load the following information for each of the field types in the columns above and be able to filter on for example "Mandatory" or "Optional", or by "Type of Date" = "Patient Identifiers":

Field Name	Mandatory or Optional Field	Field Order	Type	Format	Type of Data
Group ID	Mandatory	1	Numeric	N(11)	Patient Identifiers
Medicare Number	Optional	2	Numeric	N (12)	Patient Identifiers
MRN	Optional	3	Varchar	A (20)	Patient Identifiers
Patient Area Unique Identifier	Optional	4	Numeric	A(20)	Patient Identifiers
First Given Name	Mandatory	5	Varchar	X (40)	Patient Identifiers
Second Given Name	Optional	6	Varchar	X (40)	Patient Identifiers
Surname	Mandatory	7	Varchar	X (40)	Patient Identifiers
Alias Surname	Optional	8	Varchar	X (40)	Patient Identifiers
Sex	Mandatory	9	Numeric	N(1)	Patient Identifiers
Date of birth	Mandatory	10	Date	N(8)	Patient Identifiers

How would I do this? Would it be in a LOAD statement or am I able to load both of these as separate tables and link the column headers in one table with the rows in a second table?

Re: Allocate Field Types to Data Columns

sasikanth — Tue, 18 Dec 2018 08:09:48 GMT

HI,

try cross table load for first table and then join with second table

Test:
CrossTable(FieldName,Value)
LOAD RecNo()as Sno,* Inline [
Group ID Medicare Number MRN Patient Area Unique Identifier First Given Name Second Given Name Surname Alias Surname Sex Date of Birth
100124 44137735982 352454 1943146 Trevor AXEL AXEL TREVOR 1 5071957
100134 43432965142 660992 1721770 Perisher Terry COSBY COSBY Perisher Terry 1 19111956
100134 43432965142 660992 1721770 Perisher Terry COSBY COSBY Perisher Terry 1 19111956
100120 48189868742 745690 2886650 Tracey Warren FRYER 1 24061980
100120 48189868742 745690 2886650 Tracey Warren FRYER 1 24061980
100120 48189868742 745690 2886650 Tracey Warren FRYER 1 24061980
100014 51336986702 363094 1955234 Laurence NEWTOWN NEWTOWN LAURIE Terry 1 40619
] (delimiter is \t);

Left Join
Mapi:
Load * Inline [
FieldName Mandatory or Optional Field Field Order Type Format Type of Data
Group ID Mandatory 1 Numeric N(11) Patient Identifiers
Medicare Number Optional 2 Numeric N (12) Patient Identifiers
MRN Optional 3 Varchar A (20) Patient Identifiers
Patient Area Unique Identifier Optional 4 Numeric A(20) Patient Identifiers
First Given Name Mandatory 5 Varchar X (40) Patient Identifiers
Second Given Name Optional 6 Varchar X (40) Patient Identifiers
Surname Mandatory 7 Varchar X (40) Patient Identifiers
Alias Surname Optional 8 Varchar X (40) Patient Identifiers
Sex Mandatory 9 Numeric N(1) Patient Identifiers
Date of Birth Mandatory 10 Date N(8) Patient Identifiers
](delimiter is \t);

Re: Allocate Field Types to Data Columns

marcus_sommer — Tue, 18 Dec 2018 08:19:47 GMT

Qlik doesn't have data-types like a SQL database. There is just an automatic interpretation (mainly based on the first field-value) if it's a number or a string. In most scenarios this automatic worked very well and if not a logic like:

text(Field) as Field

covered most of the exceptions. A more special treatment is really seldom necessary.

This means the usual method in Qlik is in opposite to your approach - just loading the data and only if any adjustment is necessary it will be implemented and not using defined meta-data to load the raw-data.

Nevertheless you could do it but not by linking the meta-data to your raw-data else loading your meta-data, looping through it and creating with it - in a variable - a load-statement on-the-fly.

- Marcus