Add new line registries only for an existing ID field that has a flag changed (help pls)

[therealdees]

Hi!

I'm trying to create a historic base qvd to track new sales promotions based on campaign ID and currently unactive promotions that are now identified as active again.

There are 2 situations that I need to add a new registry. The first one (which I managed to accomplish) is to add a new registry only for a new campaign based on the campaign id (id_campanha) that is not present in the historic qvd file. The second situation I'm not sure how to do it: I need to add a new line registry with the current date for the perceived change for a reactivated campaign. Basically I have a .qvd file that will store the active campaigns for the day of the extraction. If a certain campaign id, lets say ID 001 is currently flagged as not active in the historic base (promocao_ativa = 'N') but in the new extracted data it is now flagged as active, I need to a add a new row for that same ID with a 'S' for the flag.

The closest I got was adding every flag that is signed active in the new extracted data and has a present id in the historic base, but it basically duplicates everything because it's not considering to add only if it's flagged as negative ('N') in the historic base.

Here's the code I got so far. Any help is appreciated:

If '$(vExtMetodo)' = 'ProdutosPromocoesAtivas' Then

// Extraction for the active promotions and saving in a qvd
STORE $(vExtMetodo)_Tabela into [$(vODS)/ProdutosPromocoesAtivas.qvd] (qvd);
Drop Table $(vExtMetodo)_Tabela;

//Load the historic table ProdutosPromocoes
ProdutosPromocoes:
LOAD
*
From [$(vODS)/ProdutosPromocoes.qvd] (qvd);

//Identify new campaigns and add to the historic base
Join (ProdutosPromocoes)
LOAD
cnpj_emp,
cod_produto,
custo_total_campanha,
id_campanha,
nome_campanha,
portal,
preco_promocao,
promocao_ativa,
Today() as data_cadastro,
Today() as data_ativacao,
Null() as data_desativacao
From [$(vODS)/ProdutosPromocoesAtivas.qvd] (qvd)
Where Not Exists(id_campanha, id_campanha);

// Identify reactivated campaigns and add a new registry to the historic base (THIS IS WHERE I NEED HELP)
Concatenate (ProdutosPromocoes)
LOAD
cnpj_emp,
cod_produto,
custo_total_campanha,
id_campanha,
nome_campanha,
portal,
preco_promocao,
'S' as promocao_ativa, // this is the flag field that identifies if it's active or not
Today() as data_cadastro, //It'd be a plus if I could also get this field from a previous record, as it's the first date the promotion was registred
Today() as data_ativacao, //this is the day the promotion was activated, in this case, reactivated, as it already existed
Null() as data_desativacao //this the deactivation day, it'd be null because it's current active and only changed in another routine if it's identified as deactivated
From [$(vODS)/ProdutosPromocoesAtivas.qvd] (qvd)
Where Exists(id_campanha) and promocao_ativa = 'S';

[therealdees · Accepted solution]

After lots of trying I managed to do it!!! In case someone's trying to do something similar, I'll leave the code here if it's any help. In case someone has a suggestion of something that'd be more efficient, feel free to add:

// If the method executed in the loop is for the active promotions, then start the routine

If '$(vExtMetodo)' = 'ProdutosPromocoesAtivas' Then

// Save the new extracted sample for active promotions

STORE $(vExtMetodo)_Tabela into [$(vODS)/ProdutosPromocoesAtivas.qvd] (qvd);
Drop Table $(vExtMetodo)_Tabela;

// Load the historic table for the promotions

ProdutosPromocoes:
LOAD
*
From [$(vODS)/ProdutosPromocoes.qvd] (qvd);

//Mapping table to look for the correct values for the registry date for the reactivated promotions

Mapping_DataCadastro:

Mapping Load
id_campanha,
Date(data_cadastro, 'YYYY-MM-DD') as data_cadastro
Resident ProdutosPromocoes;

//Mapping table to change the promotion flag for it's condition (active or not)

Mapping_PromocaoReativada:

Mapping Load
id_campanha,
promocao_ativa
Resident ProdutosPromocoes;

//Load the active promotions (those that the campaign id is present in the historic base) applying the mappings above to identify the reactivated ones. If it's reactivated, then it already exists)

ProdutosPromocoesReativadas:
NoConcatenate LOAD
cnpj_emp,
cod_produto,
custo_total_campanha,
id_campanha,
nome_campanha,
portal,
preco_promocao,
If(Exists(id_campanha, id_campanha), ApplyMap('Mapping_PromocaoReativada', id_campanha)) as promocao_ativa,
If(Exists(id_campanha, id_campanha), ApplyMap('Mapping_DataCadastro', id_campanha)) as data_cadastro,
Date(Today(), 'YYYY-MM-DD') as data_ativacao,
Null() as data_desativacao
From [$(vODS)/ProdutosPromocoesAtivas.qvd] (qvd)
Where Exists(id_campanha, id_campanha);

// Load in a temp table only the reactivated promotions that've been edited with the mappings

Temp:
NoConcatenate LOAD
cnpj_emp,
cod_produto,
custo_total_campanha,
id_campanha,
nome_campanha,
portal,
preco_promocao,
'S' as promocao_ativa,
Date(data_cadastro, 'YYYY-MM-DD') as data_cadastro,
Date(data_ativacao, 'YYYY-MM-DD') as data_ativacao,
Date(data_desativacao, 'YYYY-MM-DD') as data_desativacao
Resident ProdutosPromocoesReativadas
Where promocao_ativa = 'N';
Drop Table ProdutosPromocoesReativadas;

// Add the reactivated promotions to the historic table

Join(ProdutosPromocoes)
LOAD
*
Resident Temp;
Drop Table Temp;

// Add to the historic table the new identified promotions (ids that doesn't exist in the historic table)

Join(ProdutosPromocoes)
LOAD
cnpj_emp,
cod_produto,
custo_total_campanha,
id_campanha,
nome_campanha,
portal,
preco_promocao,
promocao_ativa,
Date(Today(), 'YYYY-MM-DD') as data_cadastro,
Date(Today(), 'YYYY-MM-DD') as data_ativacao,
Null() as data_desativacao
From [$(vODS)/ProdutosPromocoesAtivas.qvd] (qvd)
Where Not Exists(id_campanha, id_campanha);

//Overwrite save the updated historic base

STORE ProdutosPromocoes into [$(vODS)/ProdutosPromocoes.qvd] (qvd);
Drop Table ProdutosPromocoes;

// If the method executed in the loop is for the unactive promotions, then start the routine (the point here is to only edit the status flag and the end date for the promotions that have ended, instead of adding a new registry to the table)

ElseIf '$(vExtMetodo)' = 'ProdutosPromocoesDesat' Then

// Save the new extracted sample for unactive promotions

STORE $(vExtMetodo)_Tabela into [$(vODS)/ProdutosPromocoesDesat.qvd] (qvd);
Drop Table $(vExtMetodo)_Tabela;

// Mapping table to change the status flag for the deactivated promotions

Mapping_PromocaoDesativada:
Mapping Load
id_campanha,
'N' as promocao_ativa
From [$(vODS)/ProdutosPromocoesDesat.qvd] (qvd);

// Load the historic table with only the current active promotions for comparison purposes

Temp:
NoConcatenate LOAD
cnpj_emp,
cod_produto,
custo_total_campanha,
id_campanha,
nome_campanha,
portal,
preco_promocao,
promocao_ativa,
Date(data_cadastro, 'YYYY-MM-DD') as data_cadastro,
Date(data_ativacao, 'YYYY-MM-DD') as data_ativacao,
Date(data_desativacao, 'YYYY-MM-DD') as data_desativacao
From [$(vODS)/ProdutosPromocoes.qvd] (qvd)
Where promocao_ativa = 'S';

// Load unactive promotions in the extracted sample that are still flagged as active in the Temp table (which means they've been deactivated)

ProdutosPromocoesDesat:
NoConcatenate LOAD
cnpj_emp,
cod_produto,
custo_total_campanha,
id_campanha as id_campanha_update,
nome_campanha,
portal,
preco_promocao,
promocao_ativa as promocao_ativa_update
From [$(vODS)/ProdutosPromocoesDesat.qvd] (qvd)
Where Exists(id_campanha, id_campanha);
Drop Table Temp;

// Load the historic table and apply the mapping using the table Temp and ProdutosPromocoesDesat as a filter condition

ProdutosPromocoes:
NoConcatenate LOAD
cnpj_emp,
cod_produto,
custo_total_campanha,
id_campanha,
nome_campanha,
portal,
preco_promocao,
If(Exists(id_campanha_update, id_campanha) and promocao_ativa = 'S', ApplyMap('Mapping_PromocaoDesativada', id_campanha), promocao_ativa) as promocao_ativa,
Date(data_cadastro, 'YYYY-MM-DD') as data_cadastro,
Date(data_ativacao, 'YYYY-MM-DD') as data_ativacao,
If(Exists(id_campanha_update, id_campanha) and promocao_ativa = 'S', Date(Today(), 'YYYY-MM-DD'), Date(data_desativacao, 'YYYY-MM-DD')) as data_desativacao
From [$(vODS)/ProdutosPromocoes.qvd] (qvd);
Drop Table ProdutosPromocoesDesat;

//Overwrite save the updated historic base

STORE ProdutosPromocoes into [$(vODS)/ProdutosPromocoes.qvd] (qvd);
Drop Table ProdutosPromocoes;

End If;