topic Re: GROUP BY on Excel Import? in QlikView

GROUP BY on Excel Import?

Thu, 12 Jul 2012 10:38:12 GMT

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.

Re: GROUP BY on Excel Import?

Thu, 12 Jul 2012 11:04:27 GMT

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);

Re: GROUP BY on Excel Import?

Thu, 12 Jul 2012 11:10:21 GMT

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;

Re: GROUP BY on Excel Import?

jonathandienst — Thu, 12 Jul 2012 11:22:47 GMT

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

Re: GROUP BY on Excel Import?

Thu, 12 Jul 2012 11:32:26 GMT

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.

Re: GROUP BY on Excel Import?

Thu, 12 Jul 2012 12:02:11 GMT

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.

Re: GROUP BY on Excel Import?

Thu, 12 Jul 2012 13:01:06 GMT

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