3 Replies Latest reply: Feb 15, 2017 8:50 AM by Andy Weir RSS

    Changing file types on the go

    Adam Simek

      Hi all,

       

      I'm fairly new to Qlik Sense,

      I've recently developed a set of Apps for a client as a test for implementing "Sense" into a enterprise environment,

      the apps are linked to the same data sources (8 in total).

      The client has now provided updated refresh data which I run through my ETL,

      then I export and prepare for "Sense".

       

      The problem I "stupidly" did when building the apps, was not take into consideration data growth.

      My initial set of apps where based on excel files of just over 1m lines,

      and now I've exceeded the limits of excel to now export to csv.


      So my question is, is there an easy way to convert the Data loader to change from excel to csv ?


      Adam 

        • Re: Changing file types on the go
          Andy Weir

          Can you import the client's data into a database, SQL or access it similar do your ETL work either during or after import then connect to the tables directly during your data load taking the Excel/csv conversion out of the equation all together?

            • Re: Changing file types on the go
              Adam Simek

              Hi Andy, thanks for the reply.

              Suppose I could establish an SQL DB, but that would add another layer of complexity in my process.

              All of my ETL work is performed prior to loading into Sense, and so far I haven't seen if Sense has the same capability my ETL tool currently has (my largest data cube to date is over 65M lines from 123 different systems).

               

              I ended up replicating my app, clearing the data in the Data loader editor, and re-establishing the links with the appropriate file types etc.

               

              Seems to have worked okay, was hoping for a "on the fly" source switch..

               

                • Re: Changing file types on the go
                  Andy Weir

                  Something like this...

                   

                  Pass your type and settings in as variables.

                   

                  set vType='CSV';

                   

                  IF vType='CSV' THEN

                      SET vFile = 'lib://Documents/CSV.csv';

                      SET vFileType='txt, codepage is 1252, embedded labels, delimiter is \t, msq';

                  ELSEIF vType='EXCEL' THEN

                      SET vFile = 'lib://Documents/Excel.xlsx';

                         SET vFileType='ooxml, embedded labels, table is Sheet1';

                  ENDIF

                   

                  LOAD

                          ID

                      FROM [$(vFile)] ($(vFileType));

                   

                  It worked for me.

                   

                  Regards

                   

                  Andy