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

Load field

Hi all,

I'm trying to load a field if it contains certain information.

I know wildmatch loads values that contain certain information but I'm wondering if there is a way of loading a field if it contains certain information.

For example something like

Load

*

From Data Source

Where wildmatch(*,'Discuss Quote')

This would then load any fields that contain the words 'Discuss Quote' in it's row.

I will then do a crosstable to group these fields together into one field.

Thanks

Gareth

stevedarkhicrwunderlich

1 Solution

Accepted Solutions
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Gareth,

stalwar1‌ 's solution above is spot on.  It makes far more sense to do the cross table first and then exclude the rows.  You don't need to know the column headers as you can load with *, so:

// Load all data into crosstable

tmpTable:

CROSSTABLE(Header,Value,1)

LOAD

  1 as DummyField,  // not sure if you can omit this and have ,0 on the CROSSTABLE statement

  *

FROM [your data source]

;

// Get list of headers you are interested in

tmpHeaders:

LOAD DISTINCT

   Header as ExistsHeader

WHERE WildMatch(Value, '*Discuss Quote*');

// Load all the data in any column which you are interested in

Table:

NOCONCATENATE LOAD

  Header,

  Value

WHERE EXISTS (ExistsHeader, Header)

;

// Clean up

DROP TABLE tmpTable;

DROP TABLE tmpHeaders;

Hope that helps.


Steve

View solution in original post

12 Replies
sunny_talwar

Why not do a crosstable and then check if the field contains Discuss Quote?

Anonymous
Not applicable
Author

Good idea Sunny thanks but what would I use as my where clause?

sunny_talwar

Something like this

Table:

CrossTable (Header, Value)

LOAD ....,

     ....

FROM ...;

FinalTable:

LOAD ...

Resident Table

Where WildMatch(Header, '*Discuss Quote*');

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

If I understand your question correctly, I think you would need a number of OR statements on the WHERE to check each field in turn for the content you are looking for.


I would agree with stalwar1‌ that doing the CrossTable first and then doing a WHERE on a RESIDENT load of that table may make more sense.


Steve

Anonymous
Not applicable
Author

I can't because I don't know what the header is called.


Header 1                             Header 2                              Header 3

Discuss Quote                   Discuss Query                     Stop Payment

Discuss Problem                Discuss Quote                     Hold Payment

So I would only want to crosstable Header 1 and Header 2

Anonymous
Not applicable
Author

I've got loads of fields with very poorly laid out data.

So instead of loading each field in separately and doing a crosstable I was wondering if Qlikview could check if a field contains a string load that field if it doesn't then don't load the field.

sunny_talwar

Are you looking to get this?

Capture.PNG

Table:

CrossTable(Header, Value)

LOAD 1 as Temp,

*;

LOAD * INLINE [

    Header 1, Header 2, Header 3

    Discuss Quote, Discuss Query, Stop Payment

    Discuss Problem, Discuss Quote, Hold Payment

];


Right Join (Table)

LOAD Distinct Header

Resident Table

Where WildMatch(Value, '*Discuss Quote*');

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

When you say the "field contains a string" do you mean the field name, or the contents of that field in any row?

You could do a bit of code that loads the field list for the table and then loads distinct for each column with the where statement to then get the NoOfRows for that temporary table, and then build out the load script (list of fields) into a variable.

This feels like it would be far less efficient than having the AND statements on the WHERE.

If the issue is that you don't know what the fields are then there are ways of approaching this, which will differ depending on the data source.

MarcoWedel

please post some sample data and your expected result to clarify.