topic Re: Crosstable problem in QlikView

Crosstable problem

janne_timonen — Fri, 20 Nov 2020 18:02:30 GMT

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.

Re: Crosstable problem

robert_mika — Thu, 12 Mar 2015 11:12:41 GMT

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.

Re: Crosstable problem

janne_timonen — Thu, 12 Mar 2015 11:21:28 GMT

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.

Re: Crosstable problem

Thu, 12 Mar 2015 11:49:23 GMT

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

Re: Crosstable problem

janne_timonen — Thu, 12 Mar 2015 11:58:10 GMT

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.

Re: Crosstable problem

robert_mika — Thu, 12 Mar 2015 12:02:30 GMT

Sorry I did not see the second tab.

I will pass on this.

Maybe somebody else has solution.

Re: Crosstable problem

janne_timonen — Thu, 12 Mar 2015 12:11:40 GMT

No worries, thanks anyway.

Re: Crosstable problem

Thu, 12 Mar 2015 14:50:47 GMT

Hey,

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

Please see the attached file for a solution.

Regards

Re: Crosstable problem

prieper — Thu, 12 Mar 2015 20:27:19 GMT

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

Re: Crosstable problem

janne_timonen — Fri, 13 Mar 2015 07:06:26 GMT

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.

Re: Crosstable problem

prma7799 — Fri, 13 Mar 2015 07:20:51 GMT

Hi Janne,

Please try like this...

Re: Crosstable problem

Sat, 14 Mar 2015 05:58:49 GMT

Hi,

Try like this attachment.