Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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 ?
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.
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 ?
Can you post your script and the error you are getting?
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);
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);
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
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