topic Re: Need help creating subfields instead of using Generic load. in QlikView

Need help creating subfields instead of using Generic load.

Tue, 01 Jul 2014 13:31:35 GMT

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

Re: Need help creating subfields instead of using Generic load.

Wed, 02 Jul 2014 12:40:04 GMT

I am just wondering if this is even possible, instead of using a generic load, or if anything, a way to transform the generic load, such as if I wanted to create a master calendar from the submit date field, or to store it as a qvd? is that possible, or is it better to store the table as is as a qvd? and then do the generic load at the dashboard level

Re: Need help creating subfields instead of using Generic load.

shane_spencer — Wed, 02 Jul 2014 13:00:20 GMT

It looks like whatever you've done to create the "MsgInfoExternals.qvd.txt" was done in a crosstable when you actually want a straight table? Do you have the original data?

Re: Need help creating subfields instead of using Generic load.

Wed, 02 Jul 2014 13:03:48 GMT

That's the thing This is a cross table. Is Generic load my only option with this? .

Re: Need help creating subfields instead of using Generic load.

shane_spencer — Wed, 02 Jul 2014 13:06:33 GMT

What I'm saying is that this qvd is a CrossTable BUT you want a straight table. Do you have the raw data? If so you can create a qvd in straight table format instead of cross table.

Re: Need help creating subfields instead of using Generic load.

Wed, 02 Jul 2014 13:08:32 GMT

that is the raw data, straight from the Source db, I have made no alterations at all

Re: Need help creating subfields instead of using Generic load.

shane_spencer — Wed, 02 Jul 2014 13:11:50 GMT

To be honest I'm wondering why you want to have a Straight Table instead of a Cross Table? What are you wanting to do that cannot be done using the data in this format?

All you need to do is use Set Analysis when creating tables / charts.

Re: Need help creating subfields instead of using Generic load.

Wed, 02 Jul 2014 13:15:49 GMT

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 which are values in this cross table, Then I would like to alter the data and even add a master calendar off of submit date.

Re: Re: Need help creating subfields instead of using Generic load.

shane_spencer — Wed, 02 Jul 2014 13:25:51 GMT

What are you doing with the data though that requires those fields instead of a cross table? Look at the example attached. I've used Set Analysis within the QVW to create a chart / table that has Phone Number as a column. You can use the data in exactly the same way as a straight table.

You could create a Date field on the load using If statement. Then link to a master calendar

Re: Need help creating subfields instead of using Generic load.

Wed, 02 Jul 2014 13:35:02 GMT

Thanks for the logic ,It Is just that I need those all as fields , you stated that I can use an if condtion, in script to create that as a field?

Re: Re: Need help creating subfields instead of using Generic load.

shane_spencer — Wed, 02 Jul 2014 13:43:41 GMT

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.

Re: Need help creating subfields instead of using Generic load.

Wed, 02 Jul 2014 13:52:09 GMT

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

Re: Need help creating subfields instead of using Generic load.

Wed, 02 Jul 2014 14:01:15 GMT

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.

Re: Re: Need help creating subfields instead of using Generic load.

shane_spencer — Wed, 02 Jul 2014 14:07:00 GMT

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

Re: Re: Need help creating subfields instead of using Generic load.

shane_spencer — Wed, 02 Jul 2014 14:20:26 GMT

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

Re: Need help creating subfields instead of using Generic load.

Anonymous — Wed, 02 Jul 2014 14:22:35 GMT

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

Re: Need help creating subfields instead of using Generic load.

Wed, 02 Jul 2014 14:28:52 GMT

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.

Re: Need help creating subfields instead of using Generic load.

shane_spencer — Wed, 02 Jul 2014 14:33:39 GMT

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.

Re: Re: Need help creating subfields instead of using Generic load.

shane_spencer — Wed, 02 Jul 2014 14:47:24 GMT

Here look at this example.

Re: Need help creating subfields instead of using Generic load.

Wed, 02 Jul 2014 15:30:21 GMT

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