A very strange Qlikview Loading problem...help need urgently

[#1]

Hello,

I have a Qlikview loading problem, and am really out of ideas to resolove this.

I have three tables MMC, BOM and PV. both MMC and PV are connected to BOM. There is a measure (Qty) in PV table which i want to see against a dimension (Product_Family)  in MMC table. Now here is what happens.

If I load all the three tables at the same time from the source File and store in QVD, I can see Qty (PV Table) against Product_Family (MMC Table).

Now, if next time I reload All the data from there QVD files, then again, the report works perfectly well.

However, If load PV, and BOM from QVD files, and MMC from source file (rather than QVD), then suddenly some problem occurs and I cant see Qty (PV Table) against Product_Family (MMC Table).

I am so confused here. Its a very simple scenario. I am not doing anything complex. Simple reading from source files and storing into QVD. It seems like it only works, If I read them all from Source files at the same time, or read them all from QVD at the same time.

Does anyone have any idea or has come across any such scenario. I am really out of ideas, and I have to deliver something to Customer by monday 😞

Br

Arif

[#2]

Also, one more Issue. The second time i read from QVD and store back into QVD, the QVD size changes a bit. Is it normal? I have read the logfile and the same number of rows are being stored. there is no filter, no nothing. simply reading and storing back into QVD

Arif

[chriscammers]

Can you post your script it sounds to me like you need to consider using the "noconcatenate" keyword in your load, If your tables have identical column names Qlikview will automatically concatenate them.

[#4]

Hello, Here is the script which loads PV, MMC and BOM table. It might look complex but all it does is load these three tables.

Let me know if you need any info

PATH:
LOAD A as File_Type, 
     B as File_Path
FROM
[Paths.xls]
(biff, embedded labels, table is Sheet1$);

DATA_PATH:
NOCONCATENATE
LOAD File_Path
RESIDENT PATH
where File_Type='txt';

LET FILE_PATH_VAR=PEEK('File_Path',0,'DATA_PATH');
QVD_PATH:
NOCONCATENATE
LOAD File_Path
RESIDENT PATH
where File_Type='qvd';
LET QVD_PATH_VAR=PEEK('File_Path',0,'QVD_PATH');
Calandar:
LOAD DISTINCT PERIOD, 
     WEEK
FROM
[$(FILE_PATH_VAR)\TIME BUCKETS\DSA Time Buckets Data.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////SYNCRO PV LOAD SCRIPT/////////////////////////
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
IF FileSize('$(QVD_PATH_VAR)\SYNCRO.QVD') > 0 THEN
 [SYNCRO MAX WEEK]:
 LOAD MAXSTRING([SYNCRO Plan Week]) AS [MAX PLAN WEEK]
 FROM 
 [$(QVD_PATH_VAR)\SYNCRO.QVD](qvd);
 LET MAX_PLAN_WEEK=PEEK('MAX PLAN WEEK',0,'SYNCRO MAX WEEK');
ELSE
    SET MAX_PLAN_WEEK='2001W01';
END IF
[SYNCRO PV]:
LOAD 
  ITEMDATE,
  ITEMCONFIRMATION AS [Material confirmation],
  If(Len( ITEMCONFIRMATION)>0,'True','False') as [Item Response],
  ITEMDEMAND AS [Material demand],
  ITEM_CODE,
  SUPPLIER_CODE ,
  PLANT ,
  trim(PLAN_WEEK) AS [SYNCRO Plan Week],
  PAI_VALUE AS [Projected available Inventory],
  ITEMINTERVAL AS [Week or Period],
 trim([FORE_WEEK]) as [SYNCRO Forecasted Week]
FROM
[$(FILE_PATH_VAR)\SYNCRO\DSA PV*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
WHERE 
 PLAN_WEEK > '$(MAX_PLAN_WEEK)';

[SYNCRO temp]:
NOCONCATENATE
LOAD ITEMDATE,
  [Material confirmation], 
     [Item Response], 
     [Material demand], 
     ITEM_CODE, 
     SUPPLIER_CODE, 
     PLANT, 
     [SYNCRO Plan Week], 
     [Projected available Inventory],  
     [Week or Period], 
     [SYNCRO Forecasted Week]
RESIDENT [SYNCRO PV];
INNER JOIN ([SYNCRO temp])
LOAD DISTINCT PERIOD AS [SYNCRO Forecasted Period], 
     WEEK as [SYNCRO Forecasted Week]
RESIDENT Calandar;
INNER JOIN ([SYNCRO temp])
LOAD DISTINCT PERIOD AS [SYNCRO Plan Period], 
     WEEK as [SYNCRO Plan Week]
RESIDENT Calandar;

SYNCRO:
NOCONCATENATE
LOAD ITEMDATE,
  [Material confirmation], 
     [Item Response], 
     [Material demand], 
     trim(ITEM_CODE) as [Material Code],
     trim(SUPPLIER_CODE) as [Supplier Code],
     PLANT,
     AUTONUMBERHASH256(trim(ITEM_CODE),trim(SUPPLIER_CODE),trim(PLANT),trim([SYNCRO Forecasted Period])) AS [MTRL:SPPLR:PLNT:PRD], 
     AUTONUMBERHASH256(trim(ITEM_CODE),trim(SUPPLIER_CODE),trim(PLANT)) AS [MTRL:SPPLR:PLNT],
     [SYNCRO Plan Week], 
     [Projected available Inventory],
     [SYNCRO Plan Period],
     [SYNCRO Forecasted Period],
     [Week or Period], 
     [SYNCRO Forecasted Week]
RESIDENT [SYNCRO temp]
;
IF FileSize('$(QVD_PATH_VAR)\SYNCRO.QVD') > 0 THEN
 CONCATENATE
 
 LOAD [Material confirmation], 
      [Item Response], 
      [Material demand], 
      [Material Code], 
      [Supplier Code], 
      MTRL:SPPLR:PLNT:PRD, 
      MTRL:SPPLR:PLNT, 
      [SYNCRO Plan Week], 
      [Projected available Inventory], 
      [SYNCRO Plan Period], 
      [SYNCRO Forecasted Period], 
      [Week or Period], 
      [SYNCRO Forecasted Week]
 FROM
 [$(QVD_PATH_VAR)\SYNCRO.QVD](qvd);
END IF
DROP TABLE [SYNCRO PV];
DROP TABLE [SYNCRO temp];

STORE SYNCRO INTO [$(QVD_PATH_VAR)\SYNCRO.QVD](QVD);
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/////////////////END OF SYNCRO PV LOAD SCRIPT
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////START OF MMC HIST LOAD SCRIPT
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

IF FileSize('$(QVD_PATH_VAR)\MMC HIST.QVD') > 0 THEN
 [MMC MAX WEEK]:
 NOCONCATENATE
 LOAD MAXSTRING([MMC Plan Week]) AS [MAX PLAN WEEK]
 FROM 
 [$(QVD_PATH_VAR)\MMC HIST.QVD](qvd);
 LET MMC_MAX_PLAN_WEEK=PEEK('MAX PLAN WEEK',0,'MMC MAX WEEK');
ELSE
    SET MMC_MAX_PLAN_WEEK='2001W01';
END IF

[MMC HIST]:
NOCONCATENATE
LOAD 
  trim(PLAN_WEEK) as [MMC Plan Week],
  PRODUCT_FAMILY AS [PF:MMC Product Family], 
  trim(PRODUCT_FAMILY) AS [Product Family],
     trim(PLAN_PERIOD) as [MMC Plan Period], 
     trim(PLANT) AS [MMC Plant], 
     trim(WEEK) as [MMC Week], 
     trim(PERIOD) as [MMC Period],
     AUTONUMBERHASH256(trim(PRODUCT_FAMILY),trim(PLANT),trim(PERIOD)) as [PF:PLNT:PRD],
     AUTONUMBERHASH256(trim(PRODUCT_FAMILY),trim(PLANT)) as [PF:PLNT],
     SUM(QTY) AS [MMC QTY]
FROM
[$(FILE_PATH_VAR)\MEASURES\HIST MMC DSA Hist Plans*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
WHERE 
PLAN_WEEK>'$(MMC_MAX_PLAN_WEEK)'
AND 
MEASURE_NAME='PACK MMC'
AND PLAN_PERIOD< PERIOD
GROUP BY 
  PLAN_PERIOD , 
  PLAN_WEEK,
  PERIOD,
  WEEK , 
  PRODUCT_FAMILY, 
     PLANT;
IF FileSize('$(QVD_PATH_VAR)\MMC HIST.QVD') > 0 THEN
 CONCATENATE
 LOAD 
   [MMC Plan Week], 
      [Product Family], 
      [MMC Plan Period], 
      [MMC Plant], 
      [MMC Week], 
      [MMC Period], 
      PF:PLNT:PRD, 
      PF:PLNT, 
      [MMC QTY]
 FROM
 [$(QVD_PATH_VAR)\MMC HIST.QVD](qvd);
END IF

STORE [MMC HIST] INTO [$(QVD_PATH_VAR)\MMC HIST.QVD](qvd);

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/////////////////END OF MMC HIST LOAD SCRIPT
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/////////////////START OF BOM LOAD SCRIPT
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

IF FileSize('$(QVD_PATH_VAR)\BOM.QVD') > 0 THEN
 [BOM MAX PERIOD]:
 NOCONCATENATE
 LOAD MAXSTRING([BOM Period]) AS [MAX BOM PERIOD]
 FROM 
 [$(QVD_PATH_VAR)\BOM.QVD](qvd);
 LET BOM_MAX_PERIOD=PEEK('MAX BOM PERIOD',0,'BOM MAX PERIOD');
ELSE
    SET BOM_MAX_PERIOD='2001W01';
END IF

BOM:
NOCONCATENATE
LOAD DISTINCT
  PERIOD as [BOM Period],
  PRODUCT_FAMILY AS [Product Family],
  AUTONUMBERHASH256(trim(ITEM_CODE),trim(SUPPLIER_CODE),trim(PLANT),trim(PERIOD)) AS [MTRL:SPPLR:PLNT:PRD],
  AUTONUMBERHASH256(trim(PRODUCT_FAMILY),trim(PLANT),trim(PERIOD)) AS [PF:PLNT:PRD]
FROM
[$(FILE_PATH_VAR)\BOM\DSA BOM*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
WHERE PERIOD>'$(BOM_MAX_PERIOD)';
IF FileSize('$(QVD_PATH_VAR)\BOM.QVD') > 0 THEN
 CONCATENATE
 LOAD 
      
      [BOM Period],
      MTRL:SPPLR:PLNT:PRD, 
      PF:PLNT:PRD
 FROM
 [$(QVD_PATH_VAR)\BOM.QVD](qvd);
END IF
STORE BOM INTO [$(QVD_PATH_VAR)\BOM.QVD];

[chriscammers]

I'm not sure I know what you're doing there and I'm not sure I have to, when I have a complicated script like that I start tearing it down to the most basic parts and see where things go wonky. You do have a mix of concatenate and noconcatenate loads so there could be something there. There are also some weird behaviors I've seen with qvd's where you might want to consider staging your data into temp tables and then concatenating

On my wish list for Qlikview is to have something in the debugger that would tell you what tables exist, generally a little more visibility into what is happening in the script would be very helpful.

[#6]

Okay, let me take just one table just an example. All the three tables are being loaded the same way.. i hope now you understand what i am trying to achieve.

Basically it has three steps as explained below

1) Check for QVD files and Take maximum Period so that data after that is read from source file. If no QVD exists, then take 2001P01 so that the whole data is read

2) Read data from source file where Period is greater than maximum peirod

3) Concatenate data from QVD with data read from source files

4) Store the data back into QVD

//Check existence of QVD file, If it exists then take the maximum Period, otherwise 2001P01 should be max Period so that whole data ia read
IF FileSize('$(QVD_PATH_VAR)\BOM.QVD') > 0 THEN
 [BOM MAX PERIOD]:
 NOCONCATENATE
 LOAD MAXSTRING([BOM Period]) AS [MAX BOM PERIOD]
 FROM 
 [$(QVD_PATH_VAR)\BOM.QVD](qvd);
 LET BOM_MAX_PERIOD=PEEK('MAX BOM PERIOD',0,'BOM MAX PERIOD');
ELSE
    SET BOM_MAX_PERIOD='2001W01';
END IF
//Read data from source file where data is greater than max period.
BOM:
NOCONCATENATE
LOAD DISTINCT
  PERIOD as [BOM Period],
  PRODUCT_FAMILY AS [Product Family],
  AUTONUMBERHASH256(trim(ITEM_CODE),trim(SUPPLIER_CODE),trim(PLANT),trim(PERIOD)) AS [MTRL:SPPLR:PLNT:PRD],
  AUTONUMBERHASH256(trim(PRODUCT_FAMILY),trim(PLANT),trim(PERIOD)) AS [PF:PLNT:PRD]
FROM
[$(FILE_PATH_VAR)\BOM\DSA BOM*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
WHERE PERIOD>'$(BOM_MAX_PERIOD)';
//If QVD file exists then read data from QVD and concatenate with the data read from source file
IF FileSize('$(QVD_PATH_VAR)\BOM.QVD') > 0 THEN
 CONCATENATE
 LOAD 
      
      [BOM Period],
      MTRL:SPPLR:PLNT:PRD, 
      PF:PLNT:PRD
 FROM
 [$(QVD_PATH_VAR)\BOM.QVD](qvd);
END IF
//store data back into QVD
STORE BOM INTO [$(QVD_PATH_VAR)\BOM.QVD];

Can you please look into my script now. I hope i have made it simple now. Even in the first script that I have given, All the three tables are loaded the same way. However, In the first table, I am joining with calandar to take the period, since that table does not have period. After that, I am storing it in QVD File

Br

Arif

[nagaiank]

I did not go through your code fully to offer a solution. One typo that caught my attention was in the line below:

LET BOM_MAX_PERIOD=PEEK('MAX BOM PERIOD',0,[BOM MAX PERIOD]);

(The Peek function requires a tablename as the third argument and not a string value)

Please check the use of quoation marks in all other lines of code, as there seems to be no obvious error in your code.

Hope this helps.

[#8 · Accepted solution]

Hello,

Thank you all for your time and responses. Actually i found the culprit which was causing the issue. What i was doing was storing the autonumberhash value in the QVD files. Now when I loaded all the data from source files or from QVD, the key which was formed by autonumberhash256, always remains the same.

However, when i load the rest of tables from QVD, the keys are loaded exactly as it was stored, but one table (MMC) which was loaded this time from source files, then this time a new key is generated for it (by autonumber hash). Thus this key does not match the ones that were stored in QVDs, so thats why only the drilldown from one table to other are disturbed even the data in the same table remains the same.

One solution which i found was not to store the keys in QVD, but store the original data in QVD, and form the key after loading from QVD. This way, no matter data is loaded from QVD or source files, as long as a key is generated for a unique combination, the same key will be used in all the following tables.

Thank you all

Br

Arif

[pablolabbe]

Conclusion. 

   "Never store and reuse fields generated with autonumber or autonumberhash functions, only use this functions when reading QVDs or other source files in the final Document to optimize key field size".