Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Allocate Field Types to Data Columns

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 IDMedicare NumberMRNPatient Area Unique IdentifierFirst Given NameSecond Given NameSurnameAlias SurnameSexDate of Birth
100124441377359823524541943146Trevor AXELAXEL  TREVOR15071957
100134434329651426609921721770PerisherTerryCOSBYCOSBY  Perisher Terry119111956
100134434329651426609921721770PerisherTerryCOSBYCOSBY  Perisher Terry119111956
100120481898687427456902886650TraceyWarrenFRYER 124061980
100120481898687427456902886650TraceyWarrenFRYER 124061980
100120481898687427456902886650TraceyWarrenFRYER 124061980
100014513369867023630941955234Laurence NEWTOWNNEWTOWN  LAURIE Terry14061960

 

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 NameMandatory or Optional FieldField OrderTypeFormatType of Data
Group IDMandatory1NumericN(11)Patient Identifiers
Medicare NumberOptional2NumericN (12)Patient Identifiers
MRNOptional3VarcharA (20)Patient Identifiers
Patient Area Unique IdentifierOptional4NumericA(20)Patient Identifiers
First Given NameMandatory5VarcharX (40)Patient Identifiers
Second Given NameOptional6VarcharX (40)Patient Identifiers
SurnameMandatory7VarcharX (40)Patient Identifiers
Alias SurnameOptional8VarcharX (40)Patient Identifiers
SexMandatory9NumericN(1)Patient Identifiers
Date of birthMandatory10DateN(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?

Labels (1)
2 Replies
sasikanth
Master
Master

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

marcus_sommer

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