Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

field with multiple names

Below PIC one is what I have.

Each FacID has multiple rows

I actually use SQL to get my data.

Can I have a field multiple times and limit the field by the data in the field?

Like Fieldname = capacity as Capacity

        Fieldname = ERX Facility  as ERX Facility

   

FacIDField ValueField Name
home 3155 Capacity
home 312-15-15 PCCERX Facility
home 307-25-16V2016 (MM-DD-YY)
home 3 V2016 Clarification Needed
Home1164Capacity
Home110-06-15 PCCERX Facility
Home108-31-16V2016 (MM-DD-YY)
Home1 V2016 Clarification Needed
Home2140Capacity
Home212-01-15 PCCERX Facility
Home207-14-16V2016 (MM-DD-YY)
Home2 V2016 Clarification Needed
home4117Capacity
home411-15-16 PCCERX Facility
home408-31-16V2016 (MM-DD-YY)
home4 V2016 Clarification Needed
Home5197Capacity
Home5PCCERX Facility
Home5 V2016 (MM-DD-YY)
Home5 V2016 Clarification Needed
Need to display like this below.
FacIDCapacityERX FacilityV2016 (MM-DD-YY)V2016 Clarification Needed
home 3155 12-15-15 PCC07-25-16
Home116410-06-15 PCC08-31-16
Home214012-01-15 PCC07-14-16
home411711-15-16 PCC08-31-16
Home5197PCC

 

1 Solution

Accepted Solutions

Re: field with multiple names

Not exactly sure, but try removing these and see if it makes any difference

[FacilityCustomFields]:
LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
LOAD [FacID],
[CFID],
[CFValue];
SQL SELECT  "FacID",
  "CFID",
  "CFValue"
FROM "Fac"."dbo"."FacilityCustomFields";


Join (FacilityCustomFields)

[CustomFieldDefs]:
LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
LOAD [CFID],
  [CFType],
    [CFName],
  [CFDataType],
  [CFRequired];
SQL SELECT  "CFID",
    "CFType",
  "CFName",
  "CFDataType",
    "CFRequired"
FROM "FwLTC"."dbo"."CustomFieldDefs";

20 Replies
MVP
MVP

Re: field with multiple names

Maybe have a look at

The Generic Load

Re: field with multiple names

This might be another way to do it

Table:

LOAD * INLINE [

    FacID, Field Value, Field Name

    home 3, 155 , Capacity

    home 3, 12-15-15 PCC, ERX Facility

    home 3, 07-25-16, V2016 (MM-DD-YY)

    home 3, , V2016 Clarification Needed

    Home1, 164, Capacity

    Home1, 10-06-15 PCC, ERX Facility

    Home1, 08-31-16, V2016 (MM-DD-YY)

    Home1, , V2016 Clarification Needed

    Home2, 140, Capacity

    Home2, 12-01-15 PCC, ERX Facility

    Home2, 07-14-16, V2016 (MM-DD-YY)

    Home2, , V2016 Clarification Needed

    home4, 117, Capacity

    home4, 11-15-16 PCC, ERX Facility

    home4, 08-31-16, V2016 (MM-DD-YY)

    home4, , V2016 Clarification Needed

    Home5, 197, Capacity

    Home5, PCC, ERX Facility

    Home5, , V2016 (MM-DD-YY)

    Home5, , V2016 Clarification Needed

];

FinalTable:

LOAD Distinct FacID

Resident Table;

For i = 1 to FieldValueCount('Field Name')

  LET vField = FieldValue('Field Name', $(i));

  Left Join (FinalTable)

  LOAD FacID,

  [Field Value] as [$(vField)]

  Resident Table

  Where [Field Name] = '$(vField)';

NEXT

DROP Table Table;


Capture.PNG

Not applicable

Re: field with multiple names

I am using a direct connect.


[FacilityCustomFields]:
LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
LOAD [FacID],
[CFID],
[CFValue];
SQL SELECT  "FacID",
  "CFID",
  "CFValue"
FROM "Fac"."dbo"."FacilityCustomFields";

CustomFieldDefs]:
LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
LOAD [CFID],
[CFType],
    [CFName],
[CFDataType],
[CFRequired];
SQL SELECT  "CFID",
  "CFType",
  "CFName",
  "CFDataType",
  "CFRequired"
FROM "FwLTC"."dbo"."CustomFieldDefs";

Not applicable

Re: field with multiple names

I tried to put the Generic Load in a few different places but keeps kicking out.

I think my problem is that they are not all in one table.

Re: field with multiple names

How are these fields corresponds to the sample? What is Field Value and what is Field Name?

Not applicable

Re: field with multiple names

CFName  is field Name

CFValue  is Field value

Re: field with multiple names

May be try this:

[FacilityCustomFields]:

LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];

LOAD [FacID],

[CFID],

[CFValue];

SQL SELECT  "FacID",

  "CFID",

  "CFValue"

FROM "Fac"."dbo"."FacilityCustomFields";

Join (FacilityCustomFields)

LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];

LOAD [CFID],

[CFType],

    [CFName],

[CFDataType],

[CFRequired];

SQL SELECT  "CFID",

  "CFType",

  "CFName",

  "CFDataType",

  "CFRequired"

FROM "FwLTC"."dbo"."CustomFieldDefs";

FinalTable:

LOAD Distinct FacID,

          CFID,

          [CFType],

          [CFDataType],

          CFRequired

Resident FacilityCustomFields;

For i = 1 to FieldValueCount('CFName')

  LET vField = FieldValue('CFName', $(i));

  Left Join (FinalTable)

  LOAD FacID,

            CFID,

            [CFType],

            [CFDataType],

            CFRequired,

            [Field Value] as [$(vField)]

  Resident FacilityCustomFields

  Where [Field Name] = '$(vField)';

NEXT

DROP Table FacilityCustomFields;

Not applicable

Re: field with multiple names

I have had this before I know I am missing something but what ?

[FacilityCustomFields]:
LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
LOAD [FacID],
[CFID],
[CFValue];
SQL SELECT  "FacID",
  "CFID",
  "CFValue"
FROM "Fac"."dbo"."FacilityCustomFields";

Join (FacilityCustomFields)

[CustomFieldDefs]:
LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
LOAD [CFID],
  [CFType],
    [CFName],
  [CFDataType],
  [CFRequired];
SQL SELECT  "CFID",
    "CFType",
   "CFName",
   "CFDataType",
     "CFRequired"
FROM "FwLTC"."dbo"."CustomFieldDefs";

FinalTable:

LOAD Distinct FacID,
          CFID,
         [CFType],
          [CFDataType],
          CFRequired

Resident FacilityCustomFields;


For i = 1 to FieldValueCount('CFName')

   LET vField = FieldValue('CFName', $(i));

  Left Join (FinalTable)

  LOAD FacID,
            CFID,
            [CFType],
            [CFDataType],
            CFRequired,
            [Field Value] as [$(vField)]
  Resident FacilityCustomFields
  Where [Field Name] = '$(vField)';

NEXT

Re: field with multiple names

Not exactly sure, but try removing these and see if it makes any difference

[FacilityCustomFields]:
LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
LOAD [FacID],
[CFID],
[CFValue];
SQL SELECT  "FacID",
  "CFID",
  "CFValue"
FROM "Fac"."dbo"."FacilityCustomFields";


Join (FacilityCustomFields)

[CustomFieldDefs]:
LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
LOAD [CFID],
  [CFType],
    [CFName],
  [CFDataType],
  [CFRequired];
SQL SELECT  "CFID",
    "CFType",
  "CFName",
  "CFDataType",
    "CFRequired"
FROM "FwLTC"."dbo"."CustomFieldDefs";

Community Browser