Store statement issues

[bmenicucci]

Hi all,

I need your help as recently I've got some issues using "Store" function. Sometimes, after loading a table with several records, using the following statement

IF NoOfRows('Sales')>0 THEN

     STORE Sales INTO $(vG.ExtractPath)\TempSales.qvd (qvd);

ENDIF

returns nothing (no QVD it's been created), even though looking at the script (please see enclosed picture) you'll see a large number of records written and after executing, I can actually see a full table in Qlik. What am I missing?

Any help appreciated!

Regards

Brunello

[swuehl]

Not sure I understand what you are saying. I can see a TempSales table loaded in your script log, but no Sales table.

How do you confirm that the Sales table you want to store has actually been created and filled?

[bmenicucci]

Hi Stefan,

first of all, thanks for your prompt reply!

After loading the "temp" table, in the script I have a "NOCONCATENATE LOAD" of the Sales table that actually seems to return no rows. The strange thing is that simply cutting out the "IF" statement, it turns out to regularly creating the QVD.

Have you never had such an issue?

Brunello

[krishna_2644]

May be this:

IF NoOfRows('Sales')>0

THEN

    Sales:

     Load * from $(vG.ExtractPath)\Sales.qvd (qvd);

    STORE Sales INTO $(vG.ExtractPath)\TempSales.qvd (qvd);

    DROP TABLE Sales;

ENDIF

[swuehl]

It would be helpful to see a larger part of your script, not just the IF statement, but also the preceding LOAD statements.

[bmenicucci]

Here you go:

TempSales:

LOAD

  NumReg&'|'&TipoDocumento&'|'&NumDoc&'|'&NumRiga  AS IDSalesKey

  , XMVRFINT&'-'&'1' AS Teci_nteKey

  , XMVRFINT&'-'&'2' AS Teci_nteKey2

  , MVTIPCON&'-'&Cliente&'-'&DivDest AS %IDDesDive

  , *

  ;

LOAD

  MVSERIAL AS IDFattTest

  , MVSERIAL AS KeyDett

  , MVSERIAL AS IDBolFatDett

  , MVSERIAL AS NumReg

  , MVCLADOC AS Tipo

  , Year(MVDATDOC) AS Anno

  , MVNUMDOC AS NumDoc

  , MVDATDOC AS Data_Fattura

  , num(MVDATDOC) AS DataID

  , MVCODCON AS Cliente

  , MVCODCON AS '%IDGamma'

  , MVCODDES AS DivDest

  , MVCODPAG AS Pagamento

  , UPPER(XMVCODTI) AS U_SETT

  , MVTIPRIG

  , XMVRFINT

  , MVCODAGE AS CodAge

  , CPROWNUM AS NumRiga

  , MVCODART AS CodArt

  , MVCODART AS IDProduct

  , QTADOC AS Qta

  , TOTVEN AS Importo

  , MVUNIMIS AS Um

  , MVPREZZO AS ImpUni

  , MVPERPRO AS Provvigione

  , TOTDOC AS TotDocum

  , null() AS TotMerce

  , IMPIVA AS TotIva

  , MVTIPDOC AS TipoDocumento

  , MVCODBAN AS BanCli

  , MVSPETRA AS SpeseTrasporto

  , MVSPEIMB AS SpeseImballo

  , MVSPEINC AS SpeseIncasso

  , MVSPEBOL AS SpeseBolli

  , MVTIPCON

  , LastUpDate

FROM $(vG.ExtractPath)\VENDUTO.qvd (qvd);

CONCATENATE LOAD

   NumReg&'|'&TipoDocumento&'|'&NumDoc&'|'&NumRiga  AS IDSalesKey

   , NumReg

   , Tipo

   , Anno

   , NumDoc

   , Data_Fattura

   , DataID

   , Cliente

   , %IDGamma

   , DivDest

   , Pagamento

   , U_SETT

   , if(NOT ISNULL(CodTecnico),if(CodTecnico='FP','PF',if(CodTecnico='DR','RD',if(CodTecnico='GV','VG',CodTecnico))),if(Agente='FP','PF',if(Agente='DR','RD',if(Agente='GV','VG',Agente)))) AS CodAge

   , CodTecnico AS Storico_codTecnico

   , TotMerce

   , TotDocum

   , TotIva

   //, Anticipo

   , TipoDocumento

   , Importo

   , BanCli

   , LastUpDate

   , NumRiga

   , CodArt

   , IDProduct

   , Um

   , Qta

   , ImpUni

   , SpeTrasp AS SpeseTrasporto

   , null() AS SpeseImballo

   , speAcc AS SpeseIncasso

   , null() AS SpeseBolli

   , CodTecnico

   , 'C' AS MVTIPCON

FROM $(vG.ExtractPath)\TempStoricoSales.qvd (qvd)

WHERE NOT ISNULL(NumRiga);

LEFT JOIN (TempSales) LOAD

  ARCODART AS CodArt

  , ARTIPART AS CatMerc

  , ARGRUMER AS CodCat

FROM $(vG.ExtractPath)\ART_ICOL.qvd (qvd);

TecniciMap:

MAPPING LOAD

    Teci_nteKey

    , TICODTEC

FROM $(vG.ExtractPath)\TempKeyInterventi.qvd (qvd);

TecniciMap2:

MAPPING LOAD

    Teci_nteKey2

    , TICODTEC

FROM $(vG.ExtractPath)\TempKeyInterventi.qvd (qvd);

Sales:

NOCONCATENATE LOAD

  %IDDesDive,

  Teci_nteKey,

  Teci_nteKey2,

  CodArt,

  CodCat,

  KeyDett,

  Tipo,

  Anno,

  NumDoc,

  Data_Fattura,

  DataID,

  Cliente,

  %IDGamma,

  DivDest,

  CodAge AS Agente,

  ApplyMap('TecniciMap',Teci_nteKey,null()) AS DPCODICE,

  ApplyMap('TecniciMap2',Teci_nteKey2,null()) AS DPCODICE2,

  IF(NOT ISNULL(Teci_nteKey),'Programma Interventi','Fatturazione') AS [Provenienza Intervento],

  Provvigione,

  Pagamento,

  TotMerce,

  TotIva,

  TipoDocumento,

  U_SETT,

  BanCli,

  LastUpDate,

  NumReg,

  Importo,

  NumRiga,

  MVTIPRIG,

  XMVRFINT,

  IDProduct,

  Um,

  Qta,

  ImpUni

Resident TempSales;

JOIN LOAD

  DPCODICE

  , DPBADGE AS CodTecnico

FROM $(vG.LoadPath)Tecnici.qvd (qvd);

JOIN LOAD

  DPCODICE AS DPCODICE2

  , DPBADGE AS CodTecnico2

FROM $(vG.LoadPath)Tecnici.qvd (qvd);

DROP Table TempSales;

IF NoOfRows('Sales') >0 THEN

STORE Sales into $(vG.ExtractPath)\TempSales.qvd;

ENDIF

//AZZERO LE VARIABILI

LET vLastUpdate=;

[swuehl]

I can see a ART_ICO.qvd, but no COFMAART_ICOL.qvd.

Any reason for that?

It may also help to add TRACE statements that show the no of rows for your tables after the LOAD statements.

[bmenicucci]

no, it's only a transcription mistake...

[swuehl]

What do you mean with 'transcription mistake'? Haven't you just copied and pasted the script?

[bmenicucci]

Sorry Stefan, I'm on a mac with a Windows VM; furthermore I've been start using VS Code with Gitflow since few days and was trying the "feature" branch. In conclusion, I was unable to simply copy/paste the whole script but double checked the original one and I'm sure it is correct in that point!