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: 
Not applicable

Need help creating subfields instead of using Generic load.

I have a table that I need to create subfields in to .  all the fields are in Field propname , and the values associated to that field are in the  propvalue field associated with the amsginfoexternalid for those fields,  I did use Generic load, but I have some problems with transforming and the data was correct, however I had problems transforming the data after doing the generic load..  The fields I need created are [Customer Type] [Email Address] [Form Name Message] [Form Name] [Name On Form] [Operating Company] [Phone Number] [Question Type] [Request Type] [State] And [Submit Date]  .    also, out of curiousity , how would I implement incremental load using the QVC components script on this. ? Thank You in advance.  I have attached the script and data

19 Replies
shane_spencer
Specialist
Specialist

You've still not explained why you need them as fields - if explain what you end goal is people can help you better. As I explained you can use set analysis to create the same views / charts / tables etc.

However you could create If statements on the load. You'll need to tweek the peek setting, I've not quite got that right but you get the idea.

Not applicable
Author

My apologizes  I need them all as selectable list boxes to filter on. and I need to create other fields using these fields,  my ie was causing problems

Not applicable
Author

Pretty much i need all the values in propname as fields,  and there values are the propvalue.  Similar to what a Generic Load does I am trying to avoid using a generic load.

shane_spencer
Specialist
Specialist

Why are you avoiding the Generic Load, it's basically the inverse of a CrossTable which sounds like what you are after. have a read of this: http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

shane_spencer
Specialist
Specialist

I need them all as selectable list boxes to filter on

Again you can use Set Analysis for this. Create a table box and Hide a Column (under Presentation tab).

Anonymous
Not applicable
Author

Hi,

Generic load would be best option for your scenario however if you are not interested to use it you may go with below approach. Please note, you are creating field manually so in future if you want to add more fields you have to add them manually, they will not be added here automatically.

MsgInfoExtenrals:

LOAD %IncidentID,

     ObjId,

     aMsgInfoExternalID, 

     PropValue as [Customer Type]

FROM

MsgInfoExternals.qvd

(qvd) Where PropName='Customer Type';

Join

LOAD %IncidentID,

     ObjId,

     aMsgInfoExternalID, 

     PropValue as [Email Address]

FROM

MsgInfoExternals.qvd

(qvd) Where PropName='Email Address';

.

.

.

.

.

.

.

Join

LOAD %IncidentID,

     ObjId,

     aMsgInfoExternalID, 

     PropValue as [Name On Form]

FROM

MsgInfoExternals.qvd

(qvd) Where PropName='Name On Form';

Not applicable
Author

Thank You, I am having a hard time using the script to combine the tables and keys together like he did in the end to allow further transformations.

shane_spencer
Specialist
Specialist

I still think you could use set analysis for most of your needs as per the above example. Then you could create another table with the %IncidentID and Date (using the if command) and the two would be linked. Then your master calendar would link in to this table.

shane_spencer
Specialist
Specialist

Here look at this example.

Not applicable
Author

Thank You for this,  It seems that you guys are right generic load will be the best bet,  however  in the end I am having a hard time implementing the script  that allows all tables to join after doing a generic table.

   Set vListOfTables = ;

   For vTableNo = 0 to NoOfTables()

      Let vTableName = TableName($(vTableNo)) ;

      If Subfield(vTableName,'.',1)='GenericLabel' Then

         Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

      End If

   Next vTableNo

   CombinedGenericTable:

   Load distinct Key From GenericDB;

   For each vTableName in $(vListOfTables)

      Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];

      Drop Table [$(vTableName)];

   Next vTableName