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

adding columns after crosstable

 

I need to add a few columns after I pivoted an excel spreadsheet and I can’t find indications about where I should put the instructions.

I tried the following but the columns get added as rows (the get pivoted) J

 

CrossTable(KPI, Data)

 

LOAD F1 as "Date",

 

     [Number of Project] as [Number of Projects] ,

 

"Number of Project" / 10 as "Number of projects - KPI"

 

FROM…

 

1 Solution

Accepted Solutions
reddy-s
Master II
Master II

Hi Olivier,

Try this,

data:

CrossTable(KPI, Data)

LOAD

     [Date] as [Day],

     [Number of Project] as [Number of Projects]

FROM...

additional_data:

load *,

       year(Day)  as yearName

       resident data;

drop table data;

Once this is done, you should have the data in the "additional_data" table with an additional field "yearName"

If this docent solve the qvf, it would be easy if you attach the excel and the qvf/qvw?

View solution in original post

9 Replies
reddy-s
Master II
Master II

Hi Olivier,

try a preceding load instead:

Data:

load *,

     "Number of Project" / 10 as "Number of projects - KPI";

CrossTable(KPI, Data)


LOAD F1 as "Date",

     [Number of Project] as [Number of Projects] ,

"Number of Project" / 10 as "Number of projects - KPI"

FROM…

Not applicable
Author

Nice! preceding loads look promising

I tried


[Perf KPI]:

Load *,

  year("Date") as "Year";

CrossTable(KPI, Data)

LOAD

     [Date] as [Day],

     [Number of Project] as [Number of Projects]

FROM...

but got Field not found - <Date>

😞

Sorry for my basic questions but I'm a total newbie and all that scripting is pretty cryptic to me 😉

Not applicable
Author

I tried with "Day" instead of "Date" but then the data doesn't get unpivoted (crosstable doesn't get applied)

[Perf KPI]:

Load *,

  year("Day") as "Year";

CrossTable(KPI, Data)

LOAD

     [Date] as [Day],

     [Number of Project] as [Number of Projects]

FROM...

but

reddy-s
Master II
Master II

Hi Olivier,

as you have renamed the Date field you have that error.

Use this:

[Perf KPI]:

Load *,

  year([Day]) as "Year";

CrossTable(KPI, Data)

LOAD

     [Date] as [Day],

     [Number of Project] as [Number of Projects]

FROM...

Not applicable
Author

Thanks Sangram for your inputs, both versions work (year("Day") / year([Day]) but in both cases the data doesn't get unpivoted anymore 😞

Not applicable
Author

my WHERE at the end doesn't get applied either when the preceding load instructions are included but works fine as soon as I remove it

WHERE NOT isnull("Date")

reddy-s
Master II
Master II

Hi Olivier,

Try this,

data:

CrossTable(KPI, Data)

LOAD

     [Date] as [Day],

     [Number of Project] as [Number of Projects]

FROM...

additional_data:

load *,

       year(Day)  as yearName

       resident data;

drop table data;

Once this is done, you should have the data in the "additional_data" table with an additional field "yearName"

If this docent solve the qvf, it would be easy if you attach the excel and the qvf/qvw?

reddy-s
Master II
Master II

Is it possible for you to attach the qvf?

Not applicable
Author

Yahoo! It worked. You saved my life today (+ I learnt 2 ways to alter the data after the initial load: preceding vs Resident)

Thank you so much for your help Sangram!