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

[#1]

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

[hic]

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

[#3]

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;

[rwunderlich · Accepted solution]

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;

[#5]

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

[rwunderlich]

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

[#7]

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

[rwunderlich]

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

[rwunderlich]

"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

[#10]

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

Not one