14 Replies Latest reply: Sep 20, 2011 11:04 AM by lucas.sardo RSS

    Loading only part of a file

      Hi everyone,

       

      I'm completely new to QV and I'm in trouble with database size.

       

      I have a file, that I will call "File A", with over than 1 million registers, in this file I have informations like StoreID, Address, Number, Phone. In other file, "File B", I have a list with about 100.000 StoresID that I want to analyze, so what I need to do is, Load from File A only those StoreID that are located in File B.

       

      Can Somebody Help me with this Issue ?

       

       

      Thanks a lot.

        • Loading only part of a file

          Something like:

           

          FileB:

           

          Load

               storeid;

           

          Select

          storeid

          from FileB;

           

           

          FileA:

          Left Keep (FileB)

          Load

               storeid;

           

          Select

          storeid

          from FileA;

           

          The left keep pulls only files from File A that have the link back to File B.

            • Loading only part of a file

              marcsliving,

               

              First of all thanks for you answer.

               

              I tried to do this but I was not successfull.

               

              for example, shouldn't I specify the File Address ? or should I load it and then drop it ?

               

              LEFT KEEP is like the LEFT JOIN in SQL ?

               

              What language is this used on QV ? TSQL ?

                • Re: Loading only part of a file

                  If you go to Edit Script, and click on Table Files at the bottom you can find the table you want.

                   

                  Would look similar to this:

                   

                  LOAD

                       @1,

                       @2,

                       @3

                  FROM

                  Path to file

                  (biff, no labels, table is Sheet1$);

                   

                  You would edit the script by renaming the table:

                   

                   

                  FileB:

                  LOAD

                       @1 as storeid,

                       @2 as address,

                       @3 as employee

                  FROM

                  Path to file B

                  (biff, no labels, table is Sheet1$);

                   

                   

                  Then you do the same for the other table and add the join so it would look similar to this:

                   

                  FileB:

                  LOAD

                       @1 as storeid,

                       @2 as address,

                       @3 as employee

                  FROM

                  Path to file B

                  (biff, no labels, table is Sheet1$);

                   

                  FileA:

                  Left Keep (FileB)

                  LOAD

                       @1 as storeid,

                       @2 as storeaddress

                  FROM

                  Path to file A

                  (biff, no labels, table is Sheet1$);

                   

                   

                   

                   

                  Left Join in qlikview basically combines both tables into 1. Left Keep does the same join, but keeps the tables separate. It functions the same as SQL for the Join Type. By default Qlikview is a Full Outer Join.

                   

                  As far as exact Language I am not sure.

                    • Loading only part of a file

                      Well, I think I'm almost there.

                       

                      I'm loading a csv file but getting a ODBC error (I didn't use ODBC to load the csv) is it possible giving this commands to csv files also ?

                        • Loading only part of a file

                          Can you post your script and the error you are getting?

                            • Loading only part of a file

                              marcs, find bellow my code, my Qv is in portuguese . attached is the error message

                               

                              thanks in advance

                               

                               

                              SET ThousandSep='.';

                              SET DecimalSep=',';

                              SET MoneyThousandSep='.';

                              SET MoneyDecimalSep=',';

                              SET MoneyFormat='R$ #.##0,00;(R$ #.##0,00)';

                              SET TimeFormat='hh:mm:ss';

                              SET DateFormat='D/M/YYYY';

                              SET TimestampFormat='D/M/YYYY hh:mm:ss[.fff]';

                              SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';

                              SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

                               

                               

                              TABLE1:

                              LOAD Nome,

                                   SobreNome,

                                   [MDTR CNPJ PDV],

                                   Cidade,

                                   UF;

                              SELECT *

                              FROM

                              X:\QlikView\Aplicativos\MDTR_Visitados\Dados\PECFARMA.csv

                              (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

                              • Loading only part of a file

                                SET ThousandSep='.';

                                SET DecimalSep=',';

                                SET MoneyThousandSep='.';

                                SET MoneyDecimalSep=',';

                                SET MoneyFormat='R$ #.##0,00;(R$ #.##0,00)';

                                SET TimeFormat='hh:mm:ss';

                                SET DateFormat='D/M/YYYY';

                                SET TimestampFormat='D/M/YYYY hh:mm:ss[.fff]';

                                SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';

                                SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

                                 

                                 

                                TABLE1:

                                LOAD Nome,

                                     SobreNome,

                                     [MDTR CNPJ PDV],

                                     Cidade,

                                     UF;

                                SELECT *

                                FROM

                                X:\QlikView\Aplicativos\MDTR_Visitados\Dados\PECFARMA.csv

                                (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

                                  • Loading only part of a file

                                    SET ThousandSep='.';

                                    SET DecimalSep=',';

                                    SET MoneyThousandSep='.';

                                    SET MoneyDecimalSep=',';

                                    SET MoneyFormat='R$ #.##0,00;(R$ #.##0,00)';

                                    SET TimeFormat='hh:mm:ss';

                                    SET DateFormat='D/M/YYYY';

                                    SET TimestampFormat='D/M/YYYY hh:mm:ss[.fff]';

                                    SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';

                                    SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

                                     

                                     

                                    TABLE1:

                                    LOAD Nome,

                                         SobreNome,

                                         [MDTR CNPJ PDV],

                                         Cidade,

                                         UF;

                                    SELECT *

                                    FROM

                                    X:\QlikView\Aplicativos\MDTR_Visitados\Dados\PECFARMA.csv

                                    (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

                                     

                                    TABLE2:

                                    Left Keep (Table1)

                                    LOAD

                                         Nome,

                                         SobreNome,

                                         [MDTR CNPJ PDV],

                                         Cidade,

                                         UF;

                                    SELECT *

                                    FROM

                                    X:\QlikView\Aplicativos\MDTR_Visitados\Dados\PECFARMA.csv

                                    (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

                                     

                                    I know your second table will not look the same, but as long as you have the fields linking the tables together correctly it should work. If your store table is table 2 you can use a Right Keep(Table1) to tell it to only pull data from Table1 that exists in Table2

                                      • Loading only part of a file

                                        I don't know what I'm doing wrong. With 2 csv file it WORKS, but I try to do this by loading a QVD file, trough the Import Wizard, what happened was that I've got the "ODBC connection Failed". Should I do something different for this kind of file ?

                                          • Loading only part of a file

                                            I unfortunately have no experience working with QVD files, so I am not sure.

                                              • Loading only part of a file
                                                Jason Michaelides

                                                Please post the exact script you are using.

                                                  • Loading only part of a file

                                                    Here It is, let´s supose that "Extração MDTR Mensal_200801.qvd" is the table1 and "PECFARMA.csv" is table2.

                                                     

                                                    Sorry for the long time to reply and thank you all again

                                                     

                                                    SET ThousandSep='.';

                                                    SET DecimalSep=',';

                                                    SET MoneyThousandSep='.';

                                                    SET MoneyDecimalSep=',';

                                                    SET MoneyFormat='R$ #.##0,00;(R$ #.##0,00)';

                                                    SET TimeFormat='hh:mm:ss';

                                                    SET DateFormat='D/M/YYYY';

                                                    SET TimestampFormat='D/M/YYYY hh:mm:ss[.fff]';

                                                    SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';

                                                    SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

                                                     

                                                     

                                                     

                                                     

                                                    LOAD [MDTR Código Região],

                                                         [MDTR Código PDV Key],

                                                         [MDTR Código Distrito],

                                                         [MDTR Código Território Key],

                                                         Clie_Let,

                                                         [MDTR Código Apresentação Key],

                                                         [MDTR Código Atacadista Key],

                                                         SumControl_Units_PeriodNumber,

                                                         SumControl_Values_PeriodNumber,

                                                         [AnoMês Key],

                                                         MDTR.Quantidade,

                                                         MDTR.Valor;

                                                    SELECT *

                                                    FROM

                                                    [X:\QlikView\Aplicativos\MDTR\QVDs\Extração MDTR Mensal_200801.qvd]

                                                    (qvd);

                                                    LOAD Nome,

                                                         SobreNome,

                                                         [MDTR CNPJ PDV],

                                                         Cidade,

                                                         UF

                                                    FROM

                                                    X:\QlikView\Aplicativos\MDTR_Visitados\Dados\PECFARMA.csv

                                                    (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

                                                     

                                                     

                                                    LOAD EQUIPE,

                                                         SETOR,

                                                         [MDTR CNPJ PDV],

                                                         [DESCRICAO CAD],

                                                         [END CAD]

                                                    FROM

                                                    X:\QlikView\Aplicativos\MDTR_Visitados\Dados\CADASTRO_PDVS.xlsx

                                                    (ooxml, embedded labels, table is Sheet1);

                                                     

                                                    ODBC CONNECT TO IMS_MDTR_DBFs;

                                                     

                                                    SELECT [OUTLET_COD] AS [MDTR Código PDV Key], [OUTLET_DES], [OUTLET_ADD], [CHAN_COD], [BRICK_CODE], [CNPJ] AS [MDTR CNPJ PDV], [COMPLE_C], [REGI_PTR], [REDE_IND], [CATEGORIA], [BAIRRO], [LOGRADOURO], [TELEFONE], [CEP], [CIDADE], [UF] as [UF MDTR] FROM OUTL537A;

                                                     

                                                    SELECT * FROM PROD537A;

                                                    SELECT [PACKCODE] AS [MDTR Código Apresentação Key], * FROM PACK537A;

                                                    SELECT LEFT([PERIOD_COD],6) AS [AnoMês Key], [RATE] FROM RATE537A;

                                              • Loading only part of a file

                                                I did It !!

                                                 

                                                Thankyou all for the huge help.

                                                 

                                                Regards.

                                  • Loading only part of a file
                                    Jason Michaelides

                                    Stores:

                                    LOAD

                                       StoreID

                                    FROM FileB;

                                     

                                    Registers:

                                    LOAD

                                       *

                                    FROM FileA

                                    WHERE EXISTS(StoreID);

                                     

                                    DROP TABLE Stores; //(if you want to)

                                     

                                    That should do it.

                                     

                                    Hope this helps,

                                     

                                    Jason