1 Reply Latest reply: Oct 29, 2014 10:38 AM by Shruthi Reddy RSS

    data manipulation within FOR LOOP

    Rajender Thadakamadla

      Hi - I am new to qv, I have an existing application in which we load data from excel from different sheets (data in the sheets is same/similar but we maintain each tab for each month)  - now I would like to update this to be dynamic so I used the for loop to read the data from excel sheets but loading/storing the data into qvd I have couple of data manipulations; need to put these in the for loop

      any help is greatly appreciated


      * previously we were using concatenate to the table

      * attached includes the if conditions, manipulations

      * attached includes the for look - in this I need to put the above if conditions, manipulations etc


      thanks for your time

        • Re: data manipulation within FOR LOOP



          Once you load all the sheets data from excel. using the attache For loop.

          you could do the if manipulations.

          --After the for loop



          LOAD id as [CT ID],

               Subject as [CT Subject],

               capitalize(Status) as [CT Status],

               Date(FLOOR([Created]),'MM/DD/YYYY') AS [CT Created Date],

               year(Date(FLOOR([Created]),'MM/DD/YYYY')) as [CT Created Year],

               month(Date(FLOOR([Created]),'MM/DD/YYYY')) as [CT Created Month],

               Created as [CT Created],

               Resolved as [CT Resolved],

               LastUpdated as [CT Last Updated],

               [CF-Source] as [CT Source] ,

               [CF-Time of Compromise] as [CT Time of Compromise],

               [CF-Time of Validation] as [CT Time of Validation],

                if(Substringcount([CF-Category],' - ')>0,MID([CF-Category] ,Index([CF-Category] , '-')+2,LEN([CF-Category] ))) as [CT Category],    

               [CF-Attachment Type] as [CT Attachment Type],

               [CF-Tags] as [CT Tags],


                [CF-Attack Vector]='N/A','Internal Investigation No Attack Vector',

                IF([CF-Attack Vector]='Unknown','Attack Vector Unknown',

                if(len([CF-Attack Vector])<0,'UnCategorized',[CF-Attack Vector]))

                ) as [CT Attack Vector],        


                [CF-Threat Type]='N/A','Internal Investigation No Threat Type',

                IF([CF-Threat Type]='Unknown','Threat Type Unknown',

                if(len([CF-Threat Type])<0,'UnCategorized',[CF-Threat Type]))) as [CT Threat Type],     


               IF([CF-Kill Chain]='N/A','UnCategorized',IF([CF-Kill Chain]='Unknown','UnCategorized',[CF-Kill Chain])) as [CT Kill Chain],   

               [CF-Root Cause Identified] as [CT Root Cause Identified],

               [CF-Recommendations Made] as [CT Recommendations Made],

               [CF-Findings] as [CT Findings]

          Resident Table_With_Data;


          drop table Table_With_Data;