Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.


Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Contributor II

data manipulation within FOR LOOP

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

1 Reply
Not applicable

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;