QV and QS differences / Synthetic Keys

[Oggy172]

I'm trying to port a QV script to QS, but I am having it run out of memory in QS.

It errors on a loop.

I chucked in an exit script; to compare the two, and QS is creating a Synthetic join whereas QV doesn't - but I cannot understand why QV isn't??

Can anyone shed some light it's joined on two fields:

	/* **************************************************************** */
	//	LOAD Level 0 of the BOM											*/
	/* **************************************************************** */	
	
	_bom:
	LOAD
		RowNo()																		AS	id0,
		[Production BOM No_], 
		[Version Code]																AS	v0, 		
		If([Version Code]='', 'ORIGINAL VERSION', [Production BOM No_] 
			& '-' & [Version Code])													AS	vLink0,		

		ApplyMap('CostingBOMMap', No_, No_)											AS	No_0, 
		ApplyMap('CostingBOMMap', No_, No_)											AS	No_0_No_1_KEY, 
		
//		No_																			AS	No_0, 
//		No_																			AS	No_0_No_1_KEY, 
		
		Quantity																	AS	q0,
		[Unit of Measure Code]														AS	um0,
		[Scrap %]																	AS	s0
	FROM
	d:\qlik\datasources\qvd\BOM_Line.qvd
	(qvd)
	WHERE EXISTS ([Costing BOM No_], [Production BOM No_]);  //can use field [Costing BOM No_] since it is not qualified and has already been loaded (load top level only)
			
	//	Load Version Information for BOM

	_version:
	LOAD 
		[Production BOM No_] & '-' & [Version Code]									AS	vLink0, //links back to _bom
//	    TimeStamp#([Starting Date], 'DD/MM/YYYY hh:mm:ss')							AS	_vs0
	    TimeStamp([Starting Date], 'DD/MM/YYYY hh:mm:ss')							AS	_vs0
	FROM
		d:\qlik\datasources\qvd\BOM_Ver.qvd
	(qvd)
	WHERE EXISTS (vLink0, [Production BOM No_] & '-' & [Version Code]);

		//	Current Version

		INNER JOIN (_version)
		LOAD 
//			TimeStamp#([Starting Date], 'DD/MM/YYYY hh:mm:ss')							AS	_vs0,
			TimeStamp([Starting Date], 'DD/MM/YYYY hh:mm:ss')							AS	_vs0,
			[Production BOM No_] 														AS	_vs_bom0,
			MaxString([Version Code])													AS	v0
		FROM
			d:\qlik\datasources\qvd\BOM_Ver.qvd
		(qvd)
		WHERE EXISTS (vLink0, [Production BOM No_] & '-' & [Version Code])
//		GROUP BY TimeStamp#([Starting Date], 'DD/MM/YYYY hh:mm:ss'), [Production BOM No_];
		GROUP BY TimeStamp([Starting Date], 'DD/MM/YYYY hh:mm:ss'), [Production BOM No_];
		
		//
		
		CONCATENATE (_version)
		LOAD
			'ORIGINAL VERSION' 															AS	_vs_bom0,
			'ORIGINAL VERSION' 															AS vLink0,
			''																			AS	v0,
			Null()																		AS	_vs0
		AUTOGENERATE (1);
		
		//
		
		INNER JOIN (_bom) LOAD * RESIDENT _version;
		
		DROP TABLE _version;
	
	//	Calculate the Start Time
	
	LEFT JOIN (_bom)                          //adds fields onto the existing table _bom
	LOAD
		id0,
		[Production BOM No_],
		No_0,
//		TimeStamp(If(IsNull(_vs0), MakeDate(1990, 7, 5), TimeStamp#(_vs0, 'YYYY-MM-DD hh:mm:ss')))						AS	vs0 //resident table already in memory
		TimeStamp(If(IsNull(_vs0), MakeDate(1990, 7, 5), TimeStamp(_vs0, 'YYYY-MM-DD hh:mm:ss')))						AS	vs0 //resident table already in memory
	RESIDENT 
		_bom;
		
		DROP FIELD _vs0;     //delete old date filed NB function is "DROP FIELD"
	
	//	Calculate the End Time
	
	NOCONCATENATE _endTime:
	LOAD DISTINCT
		[Production BOM No_],
		vs0
	RESIDENT 
		_bom;

[marcus_sommer · Accepted solution]

It's both the same - with a synthetic key which will also be stated in the log-file. The difference here is that the data-model viewer in QV showed the data-sources and not the internal tables. There is an switch-option on the left side of the zoom-factor (I don't know if there are similar display options in Sense).

[Oggy172]

You are correct, thanks for confirming that I am not going insane.