12 Replies Latest reply: Mar 15, 2018 8:31 AM by Gareth Cox-Thorpe RSS

    Load field

    Gareth Cox-Thorpe

      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

       

      stevedark hic rwunderlich

        • Re: Load field
          Sunny Talwar

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

            • Re: Load field
              Gareth Cox-Thorpe

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

                • Re: Load field
                  Sunny Talwar

                  Something like this

                   

                  Table:

                  CrossTable (Header, Value)

                  LOAD ....,

                       ....

                  FROM ...;

                   

                  FinalTable:

                  LOAD ...

                  Resident Table

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

                    • Re: Load field
                      Gareth Cox-Thorpe

                      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

                        • Re: Load 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*');

                  • Re: Load field
                    Steve Dark

                    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

                    • Re: Load field
                      Gareth Cox-Thorpe

                      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.

                        • Re: Load field
                          Steve Dark

                          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.

                            • Re: Load field
                              Gareth Cox-Thorpe

                              Thanks Steve,

                               

                              I mean the contents of that field. So if the contents of any field contains 'Discuss Quote' load this. I can then add these fields that meet this criteria into a crosstable to lump them all into one field.

                               

                              I hope I'm making sense!

                               

                              Thanks

                               

                              Gareth

                                • Re: Load field
                                  Steve Dark

                                  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

                            • Re: Load field
                              Marco Wedel

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

                              • Re: Load field
                                Gareth Cox-Thorpe

                                Thanks for all your suggestions guys, I managed to build something that is very similar to stevedark suggestion. So I'm basically creating a new field (Transactions) then only loading data that contains discuss quote/amend policy etc and so long as I drop the original TempTransactions table it will only load the data I want.

                                 

                                See below

                                 

                                TempTransactions:

                                CrossTable(Temp1,Transactions)

                                Load

                                 

                                *

                                 

                                FROM

                                [\\dcn3pfsh104\home_1\CYDU\Desktop\Webchat\Webchat*.xlsx]

                                (ooxml, embedded labels, table is [Data])

                                ;

                                 

                                DROP Field Temp1;

                                 

                                Transactions:

                                NoConcatenate Load

                                 

                                *

                                 

                                Resident TempTransactions

                                 

                                 

                                Where Transactions = 'Discuss Quote' or Transactions = 'Amend/Discuss Policy' or Transactions = 'Cancel Policy' or Transactions = 'Renew Policy' or Transactions = 'Ghost Chat' or Transactions = 'Transfer Chat';

                                DROP Table TempTransactions;

                                 

                                 

                                 

                                Thanks again everyone!