Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
reddys310
Honored Contributor II

Re: adding columns after crosstable

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?

9 Replies
reddys310
Honored Contributor II

Re: adding columns after crosstable

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

Re: adding columns after crosstable

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

Re: adding columns after crosstable

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

reddys310
Honored Contributor II

Re: adding columns after crosstable

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

Re: adding columns after crosstable

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

Re: adding columns after crosstable

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")

reddys310
Honored Contributor II

Re: adding columns after crosstable

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?

reddys310
Honored Contributor II

Re: adding columns after crosstable

Is it possible for you to attach the qvf?

Not applicable

Re: adding columns after crosstable

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!

Community Browser