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

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

14 Replies
Not applicable
Author

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.

Not applicable
Author

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 ?

Not applicable
Author

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.

Not applicable
Author

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 ?

Not applicable
Author

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

Not applicable
Author

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);

Not applicable
Author

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);

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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