FUNÇÃO PARA AUTOMATIZAR CALENDARIO

[Carlinhos02]

Bom dia Preciso encontrar uma forma de colocar o campo que esta manual Anomes(202301,202212) de forma automática. 

O mentor falou que eu poderia usar o for each para criar um loop para carregar cada um dos arquivos separados e acredito que tentar usar o Min data pra pegar o 01/01/2023 e converter para 202301. 

'202208'&'CV'&CodVend as %MetaVend,
// CodVend as %Vendedor,
// F7 AS vend,
Venda as MetaVend,
LB as MetaLBVend,
"Item Médio" as "Item Medio Vend",
Pedidos as QtdPedVend,
"TKT Médio" as "TKT Medio Vend"

a parte que eu preciso automatizar é esa 202208 e CV para tudo ser autoimatico.

[AustinSpivey · Accepted solution]

You could use a load script like this to achieve this:

// Step 1
[Main]: Load Null() as [%MetaVend] AutoGenerate 0;

// Step 2
For Each vFile in FileList ('lib://DataFiles/QC_2067193_File*.qvd')
    
    // Step 3
    Let vFileInd = AutoNumber('$(vFile)');
    
    // Step 4
    [$(vFileInd)]:
    Load
        [Venda] as [MetaVend]
      , [LB] as [MetaLBVend]
      , [Item Médio] as [Item Medio Vend]
      , [Pedidos] as [QtdPedVend]
      , [TKT Médio] as [TKT Medio Vend]
      , [CodVend]
      , [Data]
    From '$(vFile)'(QVD);
    
    // Step 5
    Join ([$(vFileInd)])
    Load Distinct
        Date(Min([Data]), 'YYYYMM') as [Min Data]
    Resident [$(vFileInd)];
    
    // Step 6
    Concatenate ([Main])
    Load *
      , [Min Data] & 'CV' & CodVend as [%MetaVend]
    Resident [$(vFileInd)];
    
    // Step 7
    Drop Table  [$(vFileInd)];
    
Next vFile

How this works:

Step 1: Create an empty table that we will join our files into to ultimately use as our end table.

Step 2: Start a For Each ... in FileList() loop that loops through the files in the DataFiles folder that matches the pattern:

QC_2067193_File*.qvd

That pattern is specific to my example, your files will of course be named differently.

Step 3: Create a temporary name for our loaded table so that we can Resident Load and Drop it later. We achieve the temporary name by just using the AutoNumber() function to get a unique number based on the file path.

Step 4: Here we load the fields for our current table (this is assuming that each file has the same fields, all named the same). Note that one of the fields is a date field ([Data]) which we will use in the next step. Also note that the From statement is using:

'$(vFile)'

...which is the file path for the file we are currently on in the loop.

Step 5: Here we do a Resident Load of our first table and we use the Min() function to get the smallest [Data] value in the table. We name that new field [Min Data] and we immediately Join this new table back into the first table.

Step 6: We now Resident Load our first table again, this time using the asterisk wildcard * to load all available fields that exist in the table and then we create the [%MetaVend] field by concatenating the  [Min Data] field, the 'CV' string, and the [CodVend] field. We immediately Concatenate this table into the [Main] table that we created in Step 1.

Step 7: Now that we've created our [%MetaVend] field and moved the data for the current table to the [Main] table, we can now Drop our current table and move to the next file in our FileList() from Step 2.

I get something like this using sample data:

[Carlinhos02 · Accepted solution]

Meu amigo, ele não esta retornando liha nenhuma. poderia verificar para mim por gentileza onde está o meu erro, ta entrando no for each e saindo automaticamente, acredito que o erro está entre o file list ou esse from.

aparece a seguinte resposta:

FOLDERPATH =  LET vFolderPath = '\\KKSQLIK01\Projeto Qlik\Comercial\Planilhas\Canais de venda';

[TabelaVendedor]: Load Null() as [%MetaVend] AutoGenerate 0;

For Each vFile in FileList ('$(vFolderPath)*.xlsx')

Let vTabelaVendedorAux = AutoNumber($(vFile));

[$(vTabelaVendedorAux)]:
Load
[CodVend]
, [Vendedor]
, [Venda] as [MetaVend]
, [LB] as [MetaLBVend]
, [Item Médio] as [Item Medio Vend]
, [Pedidos] as [QtdPedVend]
, [TKT Médio] as [TKT Medio Vend]
// , [Data]
FROM '$(vFile)'
(ooxml, embedded labels, table is 'Cv_*');

Join ([$(vTabelaVendedorAux)])
Load Distinct
Date(Min([Data]), 'YYYYMM') as [Min Data]
Resident [$(vTabelaVendedorAux)];

Concatenate ([TabelaVendedor])
Load *
, [Min Data] & 'CV' & CodVend as [%MetaVend]
Resident [$(vTabelaVendedorAux)];

Drop Table [$(vTabelaVendedorAux)];

Next vFile

exit Script

[AustinSpivey]

I see three things that appear to be wrong in your script:

    1. Make sure there's a slash \ in your FileList() function after the vFolderPath variable expansion:

FileList ('$(vFolderPath)\*.xlsx')

    2.  You need to add single quotes ' ' around your vFile variable because that variable, when expanded, is the current file name, which is a string with spaces:

Let vTabelaVendedorAux = AutoNumber('$(vFile)')

    3. In your first table, you commented out the [Data] field which you would need in the second table. So the question is, if that isn't a field that you actually have in the files, what field do you need to use to get the date values from?

// , [Data]

[Carlinhos02 · Accepted solution]

Meu amigo, fiz todas as correções, agora ele esta entrando no tabela mas não entendo o motivo dele não reconhecer os campos dos dados, verifiquei na tabela da extração, os nomes estão os mesmos.

[TabelaVendedor]: Load Null() as [%MetaVend] AutoGenerate 0;

// Step 2
For Each vFile in FileList ('$(vFolderPath)\*.xlsx')

// Step 3
Let vTabelaVendedorAux = AutoNumber('$(vFile)');

// Step 4
[$(vTabelaVendedorAux)]:
Load
[CodVend]
, [Vendedor]
, [Venda] as [MetaVend]
, [LB] as [MetaLBVend]
, [Item Médio] as [Item Medio Vend]
, [Pedidos] as [QtdPedVend]
, [TKT Médio] as [TKT Medio Vend]
, [Data]

FROM '$(vFile)'
(ooxml, embedded labels, table is 'CV_*');

// Step 5
Join ([$(vTabelaVendedorAux)])
Load Distinct
Date(Min([Data]), 'YYYYMM') as [Min Data]
Resident [$(vTabelaVendedorAux)];

// Step 6
Concatenate ([TabelaVendedor])
Load *
, [Min Data] & 'CV' & CodVend as [%MetaVend]
Resident [$(vTabelaVendedorAux)];

// Step 7
Drop Table [$(vTabelaVendedorAux)];

Next vFile

exit Script

[AustinSpivey · Accepted solution]

Ahh, it looks like you are trying to use an asterisk * as a wildcard operator to try and match on the Excel worksheet name:

FROM '$(vFile)'
(ooxml, embedded labels, table is 'Cv_*');

I don't believe that will work here. You will need to do one of the following:

1. Specify the actual worksheet name without the asterisk as a wildcard;
2. Drop the "table is '...'" part entirely as long as the required worksheet will always be the first one in the Excel file; or
3. You could create an ODBC or OLE DB connection to an Excel file using the Microsoft Access Database Engine 2010 Redistributable and then use the SQLTables command to get the list of worksheet names in the Excel file, which you can then filter, search, and/or loop through to get the sheet name(s) you need:

The first downside of that last option is that you have to either create the OLE DB data connection using the QRS API, qlik-cli tool, or use a workaround where you first create the data connection in the Data Load Editor pointing to a Microsoft Access file, and then you'd update the Connection String setting in the QMC to point to your desired Excel file instead.

The second, much larger, downside of that last option is that you have to make a unique data connection for each Excel file if you are using Qlik Sense in Standard Mode.

There are a few wildcard options here, like using an Analytic Connection to R or Python to run some code to get the table names from Excel files programmatically. You could also create your own Qlik Sense connector to achieve this if it's a big enough obstacle. An interesting wildcard option would be to write some Qlik script that attempts to "unzip" the XLSX file since those files are essentially just ZIPs with several XML files therein, one of which is the workbook.xml file that includes the sheet names: