Solved

[microwin88x]

Solved

[Gysbert_Wassenaar]

Perhaps like this:

FILTERS:
LOAD
  ID_INDICATOR,
  ID_CURRENCY,
  ID_TIME,
  ID_VERSION

FROM APP.xls (ooxml, embedded labels, table is FILTERS);

INDICATOR:
LOAD
  %KEY_INDICATOR,
  ID_INDICATOR,
  INDICATOR
RESIDENT TMP_INDICATOR
WHERE EXISTS(ID_INDICATOR);
DROP TABLE TMP_INDICATOR;

CURRENCY:
LOAD
  %KEY_CURRENCY,
  ID_CURRENCY,
  CURRENCY
RESIDENT TMP_CURRENCY
WHERE EXISTS(ID_CURRENCY);
DROP TABLE TMP_CURRENCY;

TIME:
LOAD
  %KEY_TIME,
  ID_TIME,
  TIME
RESIDENT TMP_TIME
WHERE EXISTS(ID_TIME);
DROP TABLE TMP_TIME;

VERSION:
LOAD
  %KEY_VERSION,
  ID_VERSION,
  VERSION
RESIDENT TMP_VERSION
WHERE EXISTS(ID_VERSION);
DROP TABLE TMP_VERSION;

[dveloz17]

Hello microwin,

This would be a traditional way with which you could keep track of each table as it would generate one qv for each filter. You should only have encuenta the number of records smash for performance benchmarking application.

// Tabla de Filtros

FILTERS:

LOAD

  ID_INDICATOR,

     ID_CURRENCY,

     ID_TIME,

     ID_VERSION

FROM FILTERS.xlsx(ooxml, embedded labels, table is FILTERS);

LET vS_TotalFilas = NoOfRows('FILTERS')-1;

IF( $(vS_TotalFilas) > 0 ) THEN

  FOR i=0 TO $(vS_TotalFilas)

  // Asignación de Filtros

  LET V_INDICATOR = PEEK('ID_INDICATOR',$(i),'FILTERS');

  LET V_CURRENCY  = PEEK('ID_CURRENCY',$(i),'FILTERS');

  LET V_TIME      = PEEK('ID_TIME',$(i),'FILTERS');

  LET V_VERSION   = PEEK('ID_VERSION',$(i),'FILTERS');

  TRACE **********************************************;

  TRACE ************ Se genera la tablas: ************;

  TRACE ************ ID_INDICATOR: $(V_INDICATOR) ************;

  TRACE ************ CURRENCY: $(V_CURRENCY)  ************;

  TRACE ************ TIME: $(V_TIME)      ************;

  TRACE ************ VERSION: $(V_VERSION)  ************;

  // Now I have the TABs with the DETAIL tables:

  INDICATOR:

  LOAD

   %KEY_INDICATOR,

   ID_INDICATOR,

   INDICATOR

  RESIDENT TMP_INDICATOR

  WHERE ID_INDICATOR='$(V_INDICATOR)';

  DROP TABLE TMP_INDICATOR;

  CURRENCY:

  LOAD

   %KEY_CURRENCY,

   ID_CURRENCY,

   CURRENCY

  RESIDENT TMP_CURRENCY

  WHERE ID_CURRENCY='$(V_CURRENCY)';

  DROP TABLE TMP_CURRENCY;

  TIME:

  LOAD

   %KEY_TIME,

   ID_TIME,

   TIME

  RESIDENT TMP_TIME

  WHERE ID_TIME='$(V_TIME)';

  DROP TABLE TMP_TIME;

  VERSION:

  LOAD

   %KEY_VERSION,

   ID_VERSION,

   VERSION

  RESIDENT TMP_VERSION

  WHERE ID_VERSION='$(V_VERSION)';

  DROP TABLE TMP_VERSION;

  // Now I have the TAB with the FACT table:

  TMP2_BUDGET:

  LOAD

   KEY_W1          AS %KEY_INDICATOR,

   KEY_W2          AS %KEY_CURRENCY,

   KEY_W3          AS %KEY_TIME,

   KEY_W4          AS %KEY_VERSION,

   DATA            AS VALUE

  RESIDENT TMP_BUDGET

  WHERE EXISTS(%KEY_INDICATOR,KEY_W1)

  AND EXISTS(%KEY_CURRENCY,KEY_W2)

  AND EXISTS(%KEY_TIME,KEY_W3)

  AND EXISTS(%KEY_VERSION,KEY_W4);

  STORE TMP2_BUDGET INTO BUDGET_$(V_INDICATOR).qvd (qvd);

  DROP TABLES TMP2_BUDGET,INDICATOR,CURRENCY,TIME,VERSION;

  LET V_INDICATOR = NULL();

  LET V_CURRENCY  = NULL();

  LET V_TIME      = NULL();

  LET V_VERSION   = NULL();

  NEXT

  DROP TABLE FILTERS;

  LET vS_TotalFilas =  NULL();

  BUDGET:

  LOAD

  *

  FROM BUDGET_*.qvd (qvd);

  TRACE *** Se genero la tabla con: $(vS_TotalFilas) Registros de Filtros ***;

ELSE

  TRACE ************ No existen Filtros en la Tabla de FILTERS ************;

ENDIF;