Temporary Tables

Anonymous — Thu, 13 Sep 2018 07:27:17 GMT

Hi There,

Just curious if anyone out there has solutions for working with temp tables.

are there simpler ways to do temporary operations like this in Qlik?

I have inherited a bunch of queries similar to the below:

tmpAriba:
LOAD "[PO-P&I]Ship To Location (Location)" as PRNAddress,
"[PO-P&I] PLC Request Reference Number" as PLCRequestReferenceNumber,
"[PO-P&I] Order Id" as OrderId,
"[REQ] Requisition ID" as RequisitionID,
"[REQ]Requested Date (Date)" as RequestedDate,
"[PO-P&I]Target Completion Date (Date)" as TargetCompletionDate,
"[REQ] Tracking Status" as TrackingStatus,
"[PO-P&I] WOC Indicator" as WOCIndicator,
"[PO-P&I]WOC Date (Date)" as WOCDate,
"[PO-P&I] Work Program" as WorkProgram,
"[PO-P&I] Work Priority" as WorkPriority,
"[PO-P&I]Supplier (ERP Supplier)" as Supplier,
"[PO-P&I] Contract Area" as ContractArea
FROM
filename
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

////////////////////////////////////////////////////////////////////////////////////////////
// parse PRN and address data and clean field names
// this a temporary table to help build to section tables and to then re-combine.
// Excludes any admin units records...
// Need to strip out PR from "[REQ] Requisition ID" to help order this data.
////////////////////////////////////////////////////////////////////////////////////////////
tmpAriba2:
LOAD
LTrim(TextBetween(PRNAddress,'','|')) as PRN_Ariba,
LTrim(TextBetween(PRNAddress,'|','')) as Address_Ariba,
PLCRequestReferenceNumber,
OrderId,
RequisitionID,
if (index(RequisitionID,'-') > 0, TextBetween(RequisitionID, 'R', '-'), mid(RequisitionID, 3)) as RequisitionIDSortOrig,
RequestedDate, // check as a date field
   TargetCompletionDate,
TrackingStatus,
if(left(TrackingStatus,4)='EOTR',TrackingStatus,'') as ExtensionofTime,
WOCIndicator,
// MS change 5/4/2018 "[PO-P&I]WOC Date (Date)" as WOCDate, // does include unclassified string...
   if(WOCDate='Unclassified','1/1/9999',WOCDate) as WOCDate, ///*** revert??
   WorkProgram,
WorkPriority,
Supplier,
ContractArea
resident tmpAriba
where left(PRNAddress,1) <> 'C' and left(PRNAddress,1) <> 'B' and left(PRNAddress,1) <> 'G';

drop table tmpAriba;

Any assistance would be much appreciated.

Regards

Chale

Re: Temporary Tables

jwjackso — Thu, 13 Sep 2018 15:35:48 GMT

Combine the 2 queries into a load and preceeding load: https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/preceding-load.htm

Ariba:

LOAD
LTrim(TextBetween(PRNAddress,'','|')) as PRN_Ariba,
LTrim(TextBetween(PRNAddress,'|','')) as Address_Ariba,
PLCRequestReferenceNumber,
OrderId,
RequisitionID,
if (index(RequisitionID,'-') > 0, TextBetween(RequisitionID, 'R', '-'), mid(RequisitionID, 3)) as RequisitionIDSortOrig,
RequestedDate, // check as a date field
   TargetCompletionDate,
TrackingStatus,
if(left(TrackingStatus,4)='EOTR',TrackingStatus,'') as ExtensionofTime,
WOCIndicator,
// MS change 5/4/2018 "[PO-P&I]WOC Date (Date)" as WOCDate, // does include unclassified string...
   if(WOCDate='Unclassified','1/1/9999',WOCDate) as WOCDate, ///*** revert??
   WorkProgram,
WorkPriority,
Supplier,
ContractArea
where left(PRNAddress,1) <> 'C' and left(PRNAddress,1) <> 'B' and left(PRNAddress,1) <> 'G';

LOAD "[PO-P&I]Ship To Location (Location)" as PRNAddress,
"[PO-P&I] PLC Request Reference Number" as PLCRequestReferenceNumber,
"[PO-P&I] Order Id" as OrderId,
"[REQ] Requisition ID" as RequisitionID,
"[REQ]Requested Date (Date)" as RequestedDate,
"[PO-P&I]Target Completion Date (Date)" as TargetCompletionDate,
"[REQ] Tracking Status" as TrackingStatus,
"[PO-P&I] WOC Indicator" as WOCIndicator,
"[PO-P&I]WOC Date (Date)" as WOCDate,
"[PO-P&I] Work Program" as WorkProgram,
"[PO-P&I] Work Priority" as WorkPriority,
"[PO-P&I]Supplier (ERP Supplier)" as Supplier,
"[PO-P&I] Contract Area" as ContractArea
FROM
filename
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

topic Re: Temporary Tables in QlikView

Temporary Tables

Re: Temporary Tables