GROUP BY on Excel Import?

[#1]

Hiya Guys,

I have an Excel document which I have imported successfully using the script:

qvXLS:

     LOAD     Forecast_Key,
                   Profitcenter_Key,
                   DateCourse_Key,
                   Forecast_Version,
                   M_Forecast,
                   M_Forecast_Euro,
                   M_Forecast_Dkk,
                   Forecast_ByPC

     FROM $(uvInputFile)
     (ooxml, embedded labels);

     STORE qvXLS INTO "QVDFiles\$(uvOutputFile)";

However, I now need to SUM and GROUP BY these fields.  I have tried several different ways, and still cannot get this to work, eg:

qvXLS:
           LOAD Forecast_Key,
                 Profitcenter_Key,
                 DateCourse_Key,
                 Forecast_Version,
                 M_Forecast,
                 M_Forecast_Euro,
                 M_Forecast_Dkk,
                 Forecast_ByPC
           FROM $(uvInputFile)
           (ooxml, embedded labels);
    

qvSQL:
           SQL SELECT
                     Forecast_Key,
                     Profitcenter_Key,
                     DateCourse_Key,
                     Forecast_Version,
                     SUM(M_Forecast) AS M_Forecast,
                     SUM(M_Forecast_Euro) AS M_Forecast_Euro,
                     SUM(M_Forecast_Dkk) AS M_Forecast_Dkk,
                     MAX(Forecast_ByPC) AS Forecast_ByPC
                FROM qvXLS
                GROUP BY Forecast_Key, Profitcenter_Key, DateCourse_Key, Forecast_Version;

     STORE qvSQL INTO "QVDFiles\$(uvOutputFile)";

QlikView’s error-messaging is appalling - so I have no idea where to look or how to fix this.

Does anyone have any ideas?

Cheers,

Steve.

[#2]

Try this:

Load Forecast_Key, Profitcenter_Key, DateCourse_Key, Forecast_Version,
SUM(M_Forecast) AS M_Forecast,
                     SUM(M_Forecast_Euro) AS M_Forecast_Euro,
                     SUM(M_Forecast_Dkk) AS M_Forecast_Dkk,
                     MAX(Forecast_ByPC) AS Forecast_ByPC
Group by Forecast_Key, Profitcenter_Key, DateCourse_Key, Forecast_Version;
LOAD Forecast_Key,
                 Profitcenter_Key,
                 DateCourse_Key,
                 Forecast_Version,
                 M_Forecast,
                 M_Forecast_Euro,
                 M_Forecast_Dkk,
                 Forecast_ByPC
           FROM $(uvInputFile)
           (ooxml, embedded labels);

[#3]

the group by don't work when there is a From.

If you use a istruction Group by from Resident.

Exaple

Vendite_tmp:

Load a,b,c, sales

FRom vendite.csv;

Vendite:

noconcatenate

LOAd a,b,c, sum(Sales) as Sales

Resident Vendite_tmp

Group by a,b,c;

drop table Vendite_tmp;

[jonathandienst · Accepted solution]

Hi

in my experience, that's not true. This script should work fine:

qvXLS:

LOAD

          Forecast_Key,

          Profitcenter_Key,

          DateCourse_Key,

          Forecast_Version,

          SUM(M_Forecast) AS M_Forecast,

          SUM(M_Forecast_Euro) AS M_Forecast_Euro,

          SUM(M_Forecast_Dkk) AS M_Forecast_Dkk,

          MAX(Forecast_ByPC) AS Forecast_ByPC

FROM $(uvInputFile)

(ooxml, embedded labels)

Group by Forecast_Key, Profitcenter_Key, DateCourse_Key, Forecast_Version;

Hope that helps

Jonathan

[#5]

Kiran:  This works - however it is very illogical!  I would never have gotten there by myself - HOW does this work?  Does the first "LOAD" set-up the 'formatting' for the second "LOAD"? (ie: the GROUP BY?) - I just don't get how this works!

marcomasin:  I tried your method but I could not get it to work - through some testing the Script appears to fail on the line: 

STORE Vendite INTO "$(uvOutputFile)";

(as per your example above... perhaps I did something wrong?)

Jonathan:  This is logical, and works - although I thought I had already tried this!  I guess not  🙂

Thank you!

Steve.

PS:  The formatting and submission process on this Forum is terrible - QV MODS PLEASE SORT IT OUT!!!  I've just lost half my post which removed my reply to Jonathan.

[#6]

Steve,

QV has feature called preceding load which is a very beautiful. It goes like:

LOAD FirstName&MiddleName&LastName as FullName,Age;

SQL SELECT FirstName,MiddleName,LastName,Age

FROM employee.dbo.Employee;

Basically it allows multiple levels of query without using the resident load.

Its not limited to one level we can use load on top of load.

Regards,

Kiran Rokkam.

[#7]

Thanks Kiran - I will have to do some research on this!  🙂