topic Re: Re: Re: Insert a new column in script in QlikView

Insert a new column in script

Fri, 29 Aug 2014 10:22:03 GMT

Hi all,

I have input a table with columns UWYear, DevQuarters and DevP. I've also had a main data table with UWYear and DevQuarters after CrossTable. My question is how I can insert the DevP into the main data table, please? (DevP is constrained by both UWYear and DevQuarters.)

Many thanks,

Grace

Re: Insert a new column in script

MarcoWedel — Fri, 29 Aug 2014 10:23:27 GMT

please post sample data of both tables

Re: Insert a new column in script

tresB — Fri, 29 Aug 2014 10:28:12 GMT

May be like:

Final:

Load

UWYear,

DevQuarters,

DevP

From <>;

Right Join

Load

UWYear,

DevQuarters

From <Maintable>;

Re: Re: Insert a new column in script

Fri, 29 Aug 2014 10:34:35 GMT

Hi Marco,

Please find attached the sample. For both Development and Data tabs, I've done CrossTable function to them in Qlikview script so that we have UWYear as one field, and then DevelopmentQuarters (2006Q1, 2006Q2, 2006Q3, etc) as another field so that I have the contents in Development tab as field A(DevelopmentP) and the contents in Data tab as field B(Data). I'm aiming for creating a final table having four fields, UWYear, DevelopmentQuarters, DevelopmentP and Data for use.

Thanks,

Grace

Re: Re: Re: Insert a new column in script

MarcoWedel — Fri, 29 Aug 2014 23:42:14 GMT

Hi,

one solution could be:

tabFinal:
CrossTable (DevelopmentQuartersTxt, DevelopmentP)
LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/597887-122450/DevelopmentSample.xlsx] (ooxml, embedded labels, table is Development);

tabTemp:
CrossTable (DevelopmentQuartersTxt, Data)
LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/597887-122450/DevelopmentSample.xlsx] (ooxml, embedded labels, table is Data);

Join (tabFinal) LOAD * Resident tabTemp;
DROP Table tabTemp;

Left Join (tabFinal)
LOAD Distinct
  DevelopmentQuartersTxt,
  Dual(DevelopmentQuartersTxt, MakeDate(Left(DevelopmentQuartersTxt,4),Right(DevelopmentQuartersTxt,1)*3-2,1)) as DevelopmentQuarters
Resident tabFinal;

hope this helps

regards

Marco