topic Re: Using newly created (via load) field to create additional field... in QlikView

Using newly created (via load) field to create additional field...

Wed, 30 Apr 2014 14:46:38 GMT

New to QlikView, but jumping in with both feet! I've read a couple of posts about this subject and find it clear as mud. Basically, I need to use the criteria in a field that was created upon load to generate an additional field, but I'm getting stuck on when and how to call it.

Here is my original load:(its larger with many more fields, but these give you the gist)

//********* FSAData Table *********

FSA_BaseDataSAP:

LOAD [Reward Paid] as RewardPaid,

[Lead Status]as LeadStatus,

	//******* LeadStage *******
	// APPROVED

If([Reward Paid]>'0'

,'APPROVED'

// ABANDONED

If([Reward Paid]='0' AND Match([Lead Status],'Abandoned')

,'ABANDONED'

// REJECTED

If([Reward Paid]='0' AND Match([Lead Status],'Imported in Error','Rejected')

,'REJECTED'

// IN PROGRESS

If([Reward Paid]='0' AND Match([Lead Status],'AWTG_REV','Imported','In Progress','Pending','Postponed','Promoted')

,'IN PROGRESS'

,''))))as LeadStage

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

WHERE LEN([SAP Lead Number])=9;

The question: how would I code the following request to use the data within the newly created field LeadStage in the creation of another new field called LeadActioned with the following requirements:

	//******* LeadActioned *******
	// ACTIONED

If([LeadStage]>'0'AND Match([LeadStage],'ABANDONED','APPROVED','PROMOTED','REJECTED')

,'ACTIONED'

// NOT ACTIONED

If([LeadStage]>'0'AND Match([LeadStage],'AWTG_REV')

,'NOT ACTIONED'

,''))as LeadActioned,

FROM - Totally lost here as well as where to place this code

I am uncertain how/where to place the load; outside of this load completely? And how do I reference it?

FROM Resident FSA_BaseDataSAP...???

LOL - and I thought the development of LeadStage was convoluted 😉

Jan

Re: Using newly created (via load) field to create additional field...

hic — Wed, 30 Apr 2014 17:20:58 GMT

I would use a Preceding Load, i.e. a Load that loads from another Load:

Load *,

<Expression using LeadStage> as LeadActioned

;

Load *,

<Expression defining LeadStage> as LeadStage

From ... ;

HIC

Re: Using newly created (via load) field to create additional field...

Wed, 30 Apr 2014 18:59:33 GMT

Thank you Henric - I had read your post earlier as well, but I think I'm missing a simple nuance that is causing all the grief - the placement of the tablename - because now my table only contains the one field and everything else blows up, including master calender, which derives from the same load (needs DateLeadSubmitted as Min/Max). Is there another FROM I'm missing?

shortened code: (bolded is the newest code for the field I'm creating off the field created below)

WHERE do I place FSA_BaseDataSAP?

//********* FSAData Table *********

//********* LeadActioned *********

LOAD

// ACTIONED

If([LeadStage]>'0'AND Match([LeadStage],'ABANDONED','APPROVED','PROMOTED','REJECTED')

,'ACTIONED'

// NOT ACTIONED

If([LeadStage]>'0'AND Match([LeadStage],'AWTG_REV')

,'NOT ACTIONED'

,''))as LeadActioned;

FSA_BaseDataSAP: //its here right now; I've placed it in several places and all give an error for DateLeadSubmitted

LOAD [SAP Lead Number]as SAPLeadNumber,

[Reward Paid]as RewardPaid,

[Lead Type]as LeadType,

[Sub-Lead Type]as SubLeadType,

Date(Date#([Date Lead Submitted],'YYYYMMDD'),'MM/DD/YYYY')as DateLeadSubmitted,

	//******* LeadStage *******
	// APPROVED

If([Reward Paid]>'0'

,'APPROVED'

// ABANDONED

If([Reward Paid]='0' AND Match([Lead Status],'Abandoned')

,'ABANDONED'

// REJECTED

If([Reward Paid]='0' AND Match([Lead Status],'Imported in Error','Rejected')

,'REJECTED'

// IN PROGRESS

If([Reward Paid]='0' AND Match([Lead Status],'AWTG_REV','Imported','In Progress','Pending','Postponed','Promoted')

,'IN PROGRESS'

,''))))as LeadStage

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

WHERE LEN([SAP Lead Number])=9;

Re: Using newly created (via load) field to create additional field...

rwunderlich — Thu, 01 May 2014 00:37:14 GMT

It should look like this. The table label should be at the top. Note the addition of the "*" to the top LOAD which will include all fields from the lower load.

FSA_BaseDataSAP:

LOAD *,

// ACTIONED

If([LeadStage]>'0'AND Match([LeadStage],'ABANDONED','APPROVED','PROMOTED','REJECTED')

,'ACTIONED'

// NOT ACTIONED

If([LeadStage]>'0'AND Match([LeadStage],'AWTG_REV')

,'NOT ACTIONED'

,''))as LeadActioned

;

LOAD [SAP Lead Number]as SAPLeadNumber,

[Reward Paid]as RewardPaid,

[Lead Type]as LeadType,

[Sub-Lead Type]as SubLeadType,

Date(Date#([Date Lead Submitted],'YYYYMMDD'),'MM/DD/YYYY')as DateLeadSubmitted,

	//******* LeadStage *******
	// APPROVED

If([Reward Paid]>'0'

,'APPROVED'

// ABANDONED

If([Reward Paid]='0' AND Match([Lead Status],'Abandoned')

,'ABANDONED'

// REJECTED

If([Reward Paid]='0' AND Match([Lead Status],'Imported in Error','Rejected')

,'REJECTED'

// IN PROGRESS

If([Reward Paid]='0' AND Match([Lead Status],'AWTG_REV','Imported','In Progress','Pending','Postponed','Promoted')

,'IN PROGRESS'

,''))))as LeadStage

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

WHERE LEN([SAP Lead Number])=9;

Re: Using newly created (via load) field to create additional field...

Thu, 01 May 2014 14:53:36 GMT

Morning Rob!

Thank you for pointing out the missing "*"...however, when I place this asterisk in the position indicated, my Script Execution Progress window doesn't close and I don't get any errors...I tried the debugger and the script ends successfully, but again, that window doesn't close either - I'm stuck having to force it.

Its not like I don't give it time to process, the window stays open past 5 minutes...as a test I commented out that simple preceding load section and the script loads fine, except for there is only that one field in the table.

I've made no other changes to the script and while this script is long, it has less than 50k records so nothing I do should make it crank for over 5 minutes...thoughts?

Here is what the progress window says: (when I hit end and then close the program becomes unresponsive)

QuartersMap << INL66A2 12 lines fetched

FSA_BaseDataSAP << FSA 2009-05-01 thru 2009-12-31 7,890 lines fetched

FSA 2010-01-01 thru 2010-06-30 9,207 lines fetched

FSA 2010-01-01 thru 2010-06-30 << FSA 2010-07-01 thru 2010-12-31 16,332 lines fetched

FSA 2010-01-01 thru 2010-06-30 << FSA 2011-01-01 thru 2011-06-30 22,288 lines fetched

FSA 2010-01-01 thru 2010-06-30 << FSA 2011-07-01 thru 2011-12-31 27,193 lines fetched

FSA 2010-01-01 thru 2010-06-30 << FSA 2012-01-01 thru 2012-12-31 34,463 lines fetched

FSA 2010-01-01 thru 2010-06-30 << FSA 2013-01-01 thru 2013-12-31 37,484 lines fetched

FSA 2010-01-01 thru 2010-06-30 << FSA 2014-01-01 thru 2014-03-31 38,195 lines fetched

BaseDataSh@re << Sh@re-14-04-01 5,687 lines fetched

EquipmentBookings << Equipment Bookings-14-03-01$ 830 lines fetched

Submitter_BaseDataDups << Submitter-14-04-01$ 648 lines fetched

Learn@xyzcompany<< @1 1,170 lines fetched

ToddsReport << Sheet1$ 1,125 lines fetched

ManualResets << Manual Reset Dates 922 lines fetched

MinMax << FSA_BaseDataSAP 1 lines fetched

TempCal << AUTOGENERATE(240) 240 lines fetched

MasterCalendar << TempCal 240 lines fetched

The CLOSE button never becomes available.

I've pasted the entire section of script for your review, and I really appreciate your efforts - thank you!

PS-if I comment out the bolded section, the script works fine.

//********* FSAData Table *********

FSA_BaseDataSAP:

//********* LeadActioned *********

LOAD *,

// ACTIONED

If([LeadStage]>'0'AND Match([LeadStage],'ABANDONED','APPROVED','PROMOTED','REJECTED')

,'ACTIONED'

// NOT ACTIONED

If([LeadStage]>'0'AND Match([LeadStage],'AWTG_REV')

,'NOT ACTIONED'

,''))as LeadActioned;

LOAD

[SAP Lead Number]as SAPLeadNumber,

[Lead Type]as LeadType,

[Sub-Lead Type]as SubLeadType,

Date(Date#([Date Lead Submitted],'YYYYMMDD'),'MM/DD/YYYY')as DateLeadSubmitted,

Date(Date#([Date Lead Submitted],'YYYYMMDD'),'MM') as LSMonthSubmitted,

Date(Date#([Date Lead Submitted],'YYYYMMDD'),'YYYY') as LSYearSubmitted,

//********* DateLeadSubmitted FY-Q *********

If (Num(Month(Date#([Date Lead Submitted],'YYYYMMDD'))) > 09,

Date(MakeDate( Year(Date#([Date Lead Submitted],'YYYYMMDD'))+1,Num(Month(Date#([Date Lead Submitted],'YYYYMMDD'))),

Num(Day(Date#([Date Lead Submitted],'YYYYMMDD')))), 'YYYY'),

Date(Date#([Date Lead Submitted],'YYYYMMDD'),'YY')) as LSFYSubmitted,

ApplyMap('QuartersMap', month(Date(Date#([Date Lead Submitted],'YYYYMMDD'))), Null()) as LSFQSubmitted,

'FY'&If (Num(Month(Date#([Date Lead Submitted],'YYYYMMDD'))) > 09,

Date(MakeDate( Year(Date#([Date Lead Submitted],'YYYYMMDD'))+1,Num(Month(Date#([Date Lead Submitted],'YYYYMMDD'))),

Num(Day(Date#([Date Lead Submitted],'YYYYMMDD')))), 'YYYY'),

Date(Date#([Date Lead Submitted],'YYYYMMDD'),'YY'))&' '&

ApplyMap('QuartersMap', month(Date(Date#([Date Lead Submitted],'YYYYMMDD'))), Null()) as LSFYQ,

[Revenue Amount]as RevenueAmount,

[Booking Amount]as BookingAmount,

[Reward Paid]as RewardPaid,

[Business Org]as BusinessOrg,

[Lead Status]as LeadStatus,

//********* LeadStage *********

// APPROVED

If([Reward Paid]>'0'

,'APPROVED'

// ABANDONED

If([Reward Paid]='0' AND Match([Lead Status],'Abandoned')

,'ABANDONED'

// REJECTED

If([Reward Paid]='0' AND Match([Lead Status],'Imported in Error','Rejected')

,'REJECTED'

// IN PROGRESS

If([Reward Paid]='0' AND Match([Lead Status],'AWTG_REV','Imported','In Progress','Pending','Postponed','Promoted')

,'IN PROGRESS'

,''))))as LeadStage,

[Lead Status Comment]as LeadStatusComment,

If (([Reward Paid]>'1'), Date(Date#([Date Reward Paid],'YYYYMMDD'),'MM/DD/YYYY'),'')as DateRewardPaid,

Date(Date#([Date Reward Paid],'YYYYMMDD'),'MM') as RPMonthSubmitted,

Date(Date#([Date Reward Paid],'YYYYMMDD'),'YYYY') as RPYearSubmitted,

//********* DateRewardPaid FY-Q *********

If (Num(Month(Date#([Date Reward Paid],'YYYYMMDD'))) > 09,

Date(MakeDate( Year(Date#([Date Reward Paid],'YYYYMMDD'))+1,Num(Month(Date#([Date Reward Paid],'YYYYMMDD'))),

Num(Day(Date#([Date Reward Paid],'YYYYMMDD')))), 'YYYY'),

Date(Date#([Date Reward Paid],'YYYYMMDD'),'YYYY')) as RPFYSubmitted,

ApplyMap('QuartersMap', month(Date(Date#([Date Reward Paid],'YYYYMMDD'))), Null()) as RPFQSubmitted,

If (([Date Reward Paid]>1),'FY'& If (Num(Month(Date#([Date Reward Paid],'YYYYMMDD'))) > 09,

Date(MakeDate( Year(Date#([Date Reward Paid],'YYYYMMDD'))+1,Num(Month(Date#([Date Reward Paid],'YYYYMMDD'))),

Num(Day(Date#([Date Reward Paid],'YYYYMMDD')))), 'YYYY'),

Date(Date#([Date Reward Paid],'YYYYMMDD'),'YY'))&' '&

ApplyMap('QuartersMap', month(Date(Date#([Date Reward Paid],'YYYYMMDD'))), Null()),'') as RPFYQ,

[Sh@re Sales Order#]as Sh@reSalesOrder#,

[SAP Sales Order#]as SAPSalesOrder,

SSE,

[SSE Name]as SSEName,

FL,

[FL Desc]as FLDesc,

Modality,

//********* ModalitySimple *********

// AXA

If ([Modality]>'0' AND Match([Modality],'AXA')

,'AXA'

// CT

If([Modality]>'0' AND Match([Modality],'CT')

,'CT'

// MI

If([Modality]>'0' AND Match([Modality],'MI-PCL', 'MI-SPECT', 'MI-PET')

,'MI'

// MR

If([Modality]>'0' AND Match([Modality],'MR')

,'MR'

// SYNGO

If([Modality]>'0' AND Match([Modality],'SYNGO-Card', 'SYNGO-CKS','SYNGO-PACS','SYNGO-RIS','SYNGO–VIA')

,'SYNGO'

// ULT

If([Modality]>'0' AND Match([Modality],'US', 'USD - CARDIO', 'USD - GI')

,'ULT'

// XP

If([Modality]>'0' AND Match([Modality],'XP RF', 'XP WH', 'XPC', 'XPU')

,'XP'

// Other

If([Modality]>'0' AND Match([Modality],'ECS', 'OCS', 'XP A&S')

,'Other'

,'')))))))) as ModalitySimple,

[Share Lead ID]as Sh@reLeadID,

[Sh@re Opportunity]as Sh@reOpportunity,

// [Customer Name]as CustomerName,

// Street,

// City,

// Region,

// [Postal Code]as PostalCode,

// [Customer Phone Number]as CustomerPhoneNumber,

// Email,

// [Job Title]as JobTital,

Submitter as Submitter#,

[Submitter Name]as SubmitterName,

Manager as ASMManager#,

[Manager Name]as ASMManagerName,

ZVP as SrSvcDir,

[ZVP Name]as SrSvcDirName,

[Legacy Meridian ID]as LegacyMeridianID,

[Lead Additional Info]as LeadAdditionalInfo

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

WHERE LEN([SAP Lead Number])=9;

(then I go on to the next table)

////********* Sh@re Table *********

BaseDataSh@re:

blah blah blah

Re: Using newly created (via load) field to create additional field...

rwunderlich — Thu, 01 May 2014 16:23:48 GMT

Take a look at the document log to see if you can find any more clues. If you don't have a document log, enable it in the Document Properties, General tab.

-Rob

Re: Re: Using newly created (via load) field to create additional field...

Thu, 01 May 2014 19:39:49 GMT

Never had a log before - this was in the folder structure when I went to look for it - but I have NO idea how to read it; I've searched for key words like 'fail', 'error', 'execution' and I can't seem to find anything obvious...

I can tell it writes the fields and the amount of fields seems to be correct, but why the pane freezes after it states "MasterCalendar << TempCal 240 lines fetched" and does not move the active button from "End Here or Abort" to "Close" is not in English to me

I've attached it hoping you'll have better luck? The only things I changed inside the file were identifiable login info...

(Please note, I load 8 files with the load)

FROM

Jan

Re: Using newly created (via load) field to create additional field...

rwunderlich — Thu, 01 May 2014 20:03:52 GMT

My guess is that you have many synthetic keys being created and that is taking a while to generate. Run a limited load with one record to get thorough the mess and see what your data model looks like. To run a limited load:

1. In the script editor, press the Debug button.

2. Check "Limited Load" and set the count to 1.

3. Press Run.

-Rob

Re: Using newly created (via load) field to create additional field...

rwunderlich — Thu, 01 May 2014 20:11:30 GMT

"Never had a log before..."

It's a good thing. But you can wake up with a heck of a headache the first time

I recommend having the document log on for all your documents. To make it the default for new documents, Settings, User Preferences, Design, "Always use logfiles for new documents".

-Rob

Re: Re: Using newly created (via load) field to create additional field...

Thu, 01 May 2014 21:11:27 GMT

"My guess is that you have many synthetic keys being created and that is taking a while to generate"

Not one

Re: Re: Using newly created (via load) field to create additional field...

Thu, 01 May 2014 21:20:13 GMT

QuartersMap << INL785E 1 lines fetched

FSA_BaseDataSAP << FSA 2009-05-01 thru 2009-12-31 1 lines fetched

FSA 2010-01-01 thru 2010-06-30 1 lines fetched

FSA 2010-01-01 thru 2010-06-30 << FSA 2010-07-01 thru 2010-12-31 2 lines fetched

FSA 2010-01-01 thru 2010-06-30 << FSA 2011-01-01 thru 2011-06-30 3 lines fetched

FSA 2010-01-01 thru 2010-06-30 << FSA 2011-07-01 thru 2011-12-31 4 lines fetched

FSA 2010-01-01 thru 2010-06-30 << FSA 2012-01-01 thru 2012-12-31 5 lines fetched

FSA 2010-01-01 thru 2010-06-30 << FSA 2013-01-01 thru 2013-12-31 6 lines fetched

FSA 2010-01-01 thru 2010-06-30 << FSA 2014-01-01 thru 2014-03-31 7 lines fetched

BaseDataSh@re << Sh@re-14-04-01 1 lines fetched

EquipmentBookings << Equipment Bookings-14-03-01$ 1 lines fetched

Submitter_BaseDataDups << Submitter-14-04-01$ 1 lines fetched

Learn@Siemens << @1 1 lines fetched

ToddsReport << Sheet1$ 1 lines fetched

ManualResets << Manual Reset Dates 1 lines fetched

MinMax << FSA_BaseDataSAP 1 lines fetched

TempCal << AUTOGENERATE(1) 1 lines fetched

MasterCalendar << TempCal 1 lines fetched

--- Script Finished ---

NOTICE IT SAYS Script Finished...yet I cannot close that window now! After forcing the close of the program, I can go back into the script and uncomment just that newest section of code and all works as expected! Why can't I do a preceding load!

Here is a pic of my uncommented code - when I hit load, IT DOES, without issue...

Re: Using newly created (via load) field to create additional field...

rwunderlich — Thu, 01 May 2014 21:26:50 GMT

Can you post a screenshot with the code uncommented? So I can see the colorization?

When you kill the task, is it using any CPU or is it idle? Can you post the last few lines of your script?

-Rob

Re: Re: Using newly created (via load) field to create additional field...

Thu, 01 May 2014 21:42:33 GMT

I think I named the files backwards, but here is a pic of the code uncommented as well as the end of my code, which is the master calendar scripting.

I just ran the code again, and it runs beautifully if I comment out the preceding load - I'm baffled

I also exported to a script file, but I don't think that will help any unless you just want to read it in its entirety - to which I would say you have the patience of a Saint...lmao

Jan

Re: Using newly created (via load) field to create additional field...

rwunderlich — Thu, 01 May 2014 22:11:54 GMT

The QVS file you posted doesn't seem to to match the script images you posted?

Re: Re: Using newly created (via load) field to create additional field...

Thu, 01 May 2014 22:50:15 GMT

probably because it contains all the misc crap from the developer class that I commented out...there are 8 tabs of script but I only use two of them, everything else is commented...

I only have one qvw...and I exported it to you - I use Main tab for standard sets, Mapping tables for quarters, nothing on Facts or Dimensions (all commented), File Data contains the bulk of my code and what is commented out is part of files I am loading - there is nothing extraneous on that tab. Sales Person tab is all commented, the master calendar tab is all mine nothing extraneous there either. And the exit script tab is blank and always has been.

Again, the file loads perfectly if I simply comment out the preceding load - I don't know how else to prove that to you

Re: Using newly created (via load) field to create additional field...

rwunderlich — Thu, 01 May 2014 23:00:27 GMT

Got it. I see you are loading with a wildcard. Try loading just a single file to see if that works. I have run into some limitations with preceding load and wildcard filenames.

-Rob

Re: Using newly created (via load) field to create additional field...

Fri, 02 May 2014 00:25:36 GMT

YUP!!! That was it ☺

It’s always the little things!

Thanks Rob ☺