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: 
microwin88x
Creator III
Creator III

Solved

Solved

2 Replies
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;



talk is cheap, supply exceeds demand
dveloz17
Contributor
Contributor

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;

ima.png