Qlikview Scripting : crosstable

[#1]

Hello all,

1- first Question :

I have a xls file, it contains dates and values. in my script, we define the week, the weekDay and the weekYear, they are calculated from the values of dates in the xls file,  find attached( file_before_add_dimension.xlsx)

I need to modify my xls file, I need to add a dimension Resource before the dimension dates. i'm now in Qlikview scripting, and i don't know how I can add this dimension in my script.  find attached ( file_after_add_dimension.xlsx)

Script :

Dabord:

CROSSTABLE (Date, Nombre)

LOAD 'P ' & RowNo() AS Propriété,

     *

FROM [..\file_before_add_dimension.xlsx](ooxml, embedded labels, table is Feuil1);

Données:

NOCONCATENATE

LOAD Propriété, Date(num#(Date)) AS Date, Nombre

RESIDENT Dabord;

DROP Table Dabord;

Calendrier:

LOAD DISTINCT Date,

Week(Date) as Semaine,

WeekDay(Date) as Semaine_Jour,

WeekName(Date) as Semaine_Nom,

WeekYear(Date) as Semaine_Année

RESIDENT Données;

2- Second question :

I need to make link between this file after_add_dimension (after loading a new script) and another file. The new file contains Resource, some columns and Week End.

I make link between the 2 files by the column Resource. When i click on Semaine (Week (Date)) on the script i have a list of days by date in Date, but we must to select a value in Week End, i need to find this value atomatically.

for exemple : when i click on Semaine 36, i have the the list of date, and the select must be done automatically for the Week End .

i need to find that Semanie 36, it corresponds to values 01/09/2015 to 06/09/2015 and it corresponds to Week End 06-sep-15. ( now i have not this information, i need to click on 06-sep-15).

I need really your help, thank you !

[Gysbert_Wassenaar]

1. Specify that you now have two columns at the beginning of the file that shouldn't be transposed.

Script :

Dabord:

CROSSTABLE (Date, Nombre,2)

LOAD 'P ' & RowNo() AS Propriété,

     *

FROM [..\file_after_add_dimension.xlsx](ooxml, embedded labels, table is Feuil1);

2. Perhaps you can rename the weekend field so it gets the same name as the other date field. I don't know if that makes sense in your data model. If it doesn't you can try adding a Select in Field action to the OnSelect field trigger of the Semaine field to select the value you want in the other field.

[#3]

Thank you Gysbert for you response.

it works for the first, i specify that you have 2 columns. I will try what you propose for the second question.