topic Re: How do I script out the following in QlikView

How do I script out the following

Wed, 26 Mar 2014 14:45:27 GMT

I have a sheet called data and a sheet called points. I want to join the score1, score2, score3, score4 from the data sheet to the points sheet. Take the weight from the point sheet muliple it by the data sheet and sum it up for each company.

Re: How do I script out the following

arsal_90 — Wed, 26 Mar 2014 15:00:26 GMT

Please find the attached file and let me know is this the desired result

Re: How do I script out the following

Aurelien_Martinez — Wed, 26 Mar 2014 15:01:28 GMT

Hi,

CrossTable(Name,Data, 1)

LOAD Comp,

Score1,

Score2,

Score3,

Score4

FROM

[testdata.xlsx]

(ooxml, embedded labels, table is Data);

Directory;

LOAD Name,

Weight

FROM

[testdata.xlsx]

(ooxml, embedded labels, table is Weight);

Best,

Aurélien

Re: How do I script out the following

rustyfishbones — Wed, 26 Mar 2014 15:05:10 GMT

You can use CrossTable.

See the blog post currently available in your Qlik Community home tab

Regards

Alan

Re: How do I script out the following

Wed, 26 Mar 2014 15:09:23 GMT

Look at the attacched file...

Hope this helps

Re: How do I script out the following

Wed, 26 Mar 2014 17:50:22 GMT

How do I also have another column that is the sum of all the multiplied scores?

Re: How do I script out the following

arsal_90 — Thu, 27 Mar 2014 06:57:03 GMT

Can u elaborate your result in excel file. I understand what u need but if you display column wise result then it fesible for me to clearly understand your requirement

Re: Re: How do I script out the following

Thu, 27 Mar 2014 11:32:55 GMT

Look this new example in the attacchemnt.

Hope this helps.

Re: How do I script out the following

Thu, 27 Mar 2014 11:58:01 GMT

Try this

Test4:

CrossTable(Name,Score,1)
LOAD Comp,
Score1,
Score2,
Score3,
Score4
FROM
C:\testdata.xlsx
(ooxml, embedded labels, table is Data);

Left Join
LOAD Name,
Weight
FROM
C:\testdata.xlsx
(ooxml, embedded labels, table is Weight);

Chart Exp:

=SUm(Score)

=Sum(Weight)

=Sum(Score*Weight)

If this is not correct then reply with your required result..

Re: How do I script out the following

stevedark — Thu, 27 Mar 2014 12:00:33 GMT

With a left join there you run the risk of duplicating rows in the main table with duplicate weights. I would suggest an ApplyMap, or simply leaving as an association.