Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help in crosstable

Hello everyone~

I just want to ask for help in this certain document.

What will be the approach?

I have tried transforming the table using transpose, fill and finally crosstable.

I can't find the desired output.

my output should be like this:

Dept Code | Status | IP(actual) | OP(actual) | IP(budget) | OP(budget)

please see attached file.

Thanks,

-Donna

7 Replies
Not applicable
Author

Hi,

Use below code you will get data..

 

LOAD

F1 as [Dept Code],
F2 as Status,
IP as IPActual,
OP as OPActual,
IP1 as IPBudget,
OP1 as OPBudget
FROM
Actual_Rev_1.xlsx
(
ooxml, embedded labels, header is 2 lines, table is

Sheet1);

Not applicable
Author

Hi,

Thanks but I needed the Month since i still have files including January till September~

Not applicable
Author

Hi,

is that month coming as filename or is it a field name in your data..

If the month coming as filename then use filename function..

if possible post sample file...

Not applicable
Author

the month is coming from a filename

Not applicable
Author

Hey, can i have the sample file name..

Not applicable
Author

Jan2012, Feb2012, Mar2012, Apr2012, Jun2012, July2012, Aug2012, Sep2012

Not applicable
Author

Hi,

use like this

  

LOAD

 

F1 as [Dept Code],
F2 as Status,
IP as IPActual,
OP as OPActual,
IP1 as IPBudget,
OP1 as OPBudget,

Filename() as Monthyear
FROM
Actual_Rev_1.xlsx
(
ooxml, embedded labels, header is 2 lines, table is

Sheet1);

still if you want only month the use

left(Filename(),3) as Month