Crosstable problem

[janne_timonen]

Hi all,

I'm stuck with a problem loading an excel file to qv, . I've attached a sample file that shows the structure of the original file as qv file wizard interprets it and the desired outcome.

Row 1 is a header row and I need to use field 'dept a', 'dept b' and so on as field values and skip the others. Column B is a date field in num format. Column A is just weekday as a text and refers to last years figures (those do not have reference to actual date data).

I guess a crosstable would do the trick, but I can't figure out the correct script. Any help would be appreciated.

[robert_mika]

You can press x on the column you do not need and then use this script

Directory;

LOAD [dept a],

     [dept b]

FROM

[sample (1).xlsx]

(ooxml, embedded labels, table is original)

WHERE RecNo() > 5 AND RecNo() < 1500

you can change the 1500.

[janne_timonen]

Hi,

Thanks Robert, but I need those columns. For every dept I need Actuals for this year and last year, containing € and amount of customers.

There's second sheet 'desired result' in the sample file.

[#4]

Hi,

In scripting, do like this.

CrossTable([dept a], [dept b])

LOAD f1,

     f2,

     [dept a],

     f3,

     f4,

     f5,

     [dept b],

     f7,

     f8,

     f9,

     [and so on, around 20 depts.]

FROM

(ooxml, embedded labels, table is original);

Thanks,

krish

[janne_timonen]

Hi krishnama,

Could you provide a sample what your result table looks like with that script? I tried and my result wasn't as I wanted it to be.

[robert_mika]

Sorry I did not see the second tab.

I will pass on this.

Maybe somebody else has solution.

[janne_timonen]

No worries, thanks anyway.

[#8]

Hey,

I believe you might need to think through your data model.

Please see the attached file for a solution.

Regards

RL

[prieper]

Have excluded the problem with the shift in the dates, i.e. to have a weekday and a date with the associated data not being in line.

BTW: How would this table look in case of a leap year?

So basically inserted field on the 2014-data to be in exactly the same row as 2015.

If you wish to adjust, you may bring in a counter in the columns and treat all 2014-columns different than 2015 ....

Sample is attached

Have Fun! Peter

[janne_timonen]

Thanks guys, you gave me couple of nice different approaches.

Anyways, I learned a valuable lesson yesterday: Always double check what part of the task was the requirement and what was just 'nice to have'. In fact I just needed one column.