topic Re: Add Static Data to a Data Load in QlikView

Add Static Data to a Data Load

evansabres — Wed, 29 Jun 2016 18:03:18 GMT

Hello -

I am pulling in data from a data source, my script looks likes this:

Data:

LOAD

dim_date as PSEdte,

makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as Date,

metric_users as PSEuniquevisits,

metric_sessions as PSEtotalvisits,

metric_pageviews as PSEpageviews

FROM [data source];

I have a column, called acctNME, that I want to add in. Currently, that field is in located in an inline table:

Accounts:

LOAD * INLINE

[acctID, acctNME

123, name

];

So, ultimately, I would like for my table 'Data' to include the field 'acctNME'

Is this possible?

Thank you.

Re: Add Static Data to a Data Load

karthikoffi27se — Wed, 29 Jun 2016 18:36:12 GMT

Hi Evan,

There is possibility to include acctNAME from Inline table to Data table unless there is acctID in your data source, if that's not the case means you are requirement is to analyse each individual account uniquely then you can add in another field in your inline table.

For Eg:

 Data:

LOAD

dim_date as PSEdte,

makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as Date,

metric_users as PSEuniquevisits,

metric_sessions as PSEtotalvisits,

metric_pageviews as PSEpageviews,

1 as acctID_Identifier

FROM [data source];

Accounts:

Load * Inline[

acctID_Identifier,acctID,acctName

1,123,name

];

If you load the table Qlik will make the joins to acctID_Identifier.

If there is more then one acctID then we have to find a possible composite key to join acctName.

Hope this helps.

Many Thanks

Karthik

Re: Add Static Data to a Data Load

trdandamudi — Wed, 29 Jun 2016 18:50:56 GMT

You can like below:

Data:

LOAD

dim_date as PSEdte,

makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as Date,

metric_users as PSEuniquevisits,

metric_sessions as PSEtotalvisits,

metric_pageviews as PSEpageviews

FROM [data source];

I have a column, called acctNME, that I want to add in. Currently, that field is in located in an inline table:

Accounts:

Concatenate

LOAD * INLINE

[acctNME

name

];

But check the output and see if that is what you want.

Re: Add Static Data to a Data Load

evansabres — Wed, 29 Jun 2016 18:57:08 GMT

This does add the columns to the table, however, does not fill in all of the rows with the acctNME

So, my script now looks like:

ENTITY:

LOAD

dim_date as PSEdte,

makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as Date,

metric_users as PSEuniquevisits,

metric_sessions as PSEtotalvisits,

metric_pageviews as PSEpageviews

FROM

[Data Source]

(qvx);

Accounts:

Concatenate

LOAD * INLINE

[acctID, acctNME

1234567, NameName

];

Re: Add Static Data to a Data Load

trdandamudi — Wed, 29 Jun 2016 19:13:11 GMT

In order to fill all the columns with AcctName you need have acctID field in the "ENTITY" table. If you have this field then you can do a mapping load and get the acctname. If it is only one account then you can use Join.

Re: Add Static Data to a Data Load

maxgro — Wed, 29 Jun 2016 19:14:52 GMT

Data:

LOAD

dim_date as PSEdte,

makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as Date,

metric_users as PSEuniquevisits,

metric_sessions as PSEtotalvisits,

metric_pageviews as PSEpageviews,

'name' as acctNME

FROM [data source];

or (if you have only one row in Data)

Data:

LOAD

dim_date as PSEdte,

makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as Date,

metric_users as PSEuniquevisits,

metric_sessions as PSEtotalvisits,

metric_pageviews as PSEpageviews

FROM [data source];

join (Data):

LOAD * INLINE

[acctID, acctNME

123, name

];

Re: Add Static Data to a Data Load

evansabres — Wed, 29 Jun 2016 19:15:29 GMT

Yes, unfortunately neither of the fields are in the 'Entity' table, therein lies the problem. I am trying to add that column in my scripting