Add Static Data to a Data Load

[evansabres]

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.

[karthikoffi27se]

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

[trdandamudi]

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.

[evansabres]

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

  ];

[trdandamudi]

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.

[maxgro · Accepted solution]

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

];

[evansabres]

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