Join Crosstable

[victor_greffet]

Hi Community,

I am beginner in Qlikview development.

In my app i wanted to join some "crosstabled" tables in order to have a specific structure.

My loaded data in a qvd file:

ID	Step 1 TargetDate	Step 1 ValidateDate	Step 1 ForecastDate	Step 2 TargetDate	Step 2 ValidateDate	Step 2 ForecastDate	.....	Step 5 ForecastDate
No	date	date	date	date	date	date	.....	date
No	date	date	date	date	date	date	.....	date
No	date	date	date	date	date	date	.....	date
No	...	....	....	....	....	....	...	....

what I want :

ID	STEP	TargetDates	ValidateDates	ForecastDates
No	step	date	date	date
No	step	date	date	date
...	....	...	....	....

What I did :

[DATA]:

Crosstable(No, TargetDates) LOAD

    ID,

Step 1 TargetDate,

Step 2 TargetDate,

Step 3 TargetDate,

                           Step 4 TargetDate,

                           Step 5 TargetDate,

FROM [lib://BXKD/DT_20171002_WK40.QVD]

(qvd);

Left join (DATA)

// [VALIDATE_DATE]:

Crosstable(No, ValidateDates) LOAD

    ID,

                         Step 1 ValidateDate,

                          Step 2 ValidateDate,

                          Step 3 ValidateDate,

                           Step 4 ValidateDate,

                           Step 5 ValidateDate,

FROM [lib://BXKD/DT_20171002_WK40.QVD]

(qvd);

Left join (DATA)

//[FORECAST_DATE]:

Crosstable(No, ForecastDates) LOAD

ID,

                          Step 1 ForecastDate,

                          Step 2 ForecastDate,

                          Step 3 ForecastDate,

                           Step 4 ForecastDate,

                           Step 5 ForecastDate,

FROM [lib://BXKD/DT_20171002_WK40.QVD]

(qvd);

But after executing the script I have an error message :

Thank you for your help!

[eduardo_dimperio]

     Hi Victor,

First, did you loaded separated cross table to be sure that's are in correct format to you?

Second, Did you load all cross tables without joins?

[kaanerisen · Accepted solution]

Hi,

Here is a sample code for you.

temp:

CrossTable(Type,Date,1)

Load * Inline [

No,Step1-TargetDate,Step1-ValidateDate,Step1-ForecastDate,Step2-TargetDate,Step2-ValidateDate,Step2-ForecastDate

1,20170301,20170401,20170501,20170601,20170701,20170801

2,20180301,20180401,20180501,20180601,20180701,20180801

3,20190301,20190401,20190501,20190601,20190701,20190801

];

Main:

load distinct

No,SubField(Type,'-',1) as Step

Resident temp;

left join(Main)

load

No,SubField(Type,'-',1) as Step,Date as TargetDate

Resident temp

where SubField(Type,'-',2)='TargetDate';

left join(Main)

load

No,SubField(Type,'-',1) as Step,Date as ValidateDate

Resident temp

where SubField(Type,'-',2)='ValidateDate';

left join(Main)

load

No,SubField(Type,'-',1) as Step,Date as ForecastDate

Resident temp

where SubField(Type,'-',2)='ForecastDate';

Drop Table temp;

[victor_greffet]

This is awesome, thank you, i hope it will be usefull for the community.

[kaanerisen]

Hi,

Here is a more generic way to achieve your goal. With this kind of script, you can get your desired output without adding the script even different datetype columns will be added the source.

temp:

CrossTable(Type,Date,1)

Load * Inline [

No,Step1-TargetDate,Step1-ValidateDate,Step1-ForecastDate,Step2-TargetDate,Step2-ValidateDate,Step2-ForecastDate

1,20170301,20170401,20170501,20170601,20170701,20170801

2,20180301,20180401,20180501,20180601,20180701,20180801

3,20190301,20190401,20190501,20190601,20190701,20190801

];

temp_datetype:

load distinct SubField(Type,'-',2) as datetype Resident temp;

Main:

load distinct

No,SubField(Type,'-',1) as Step

Resident temp;

FOR Each a in FieldValueList('datetype')

  left join(Main)

  load

    No,

    SubField(Type,'-',1) as Step,

    Date as '$(a)'

  Resident temp

  where SubField(Type,'-',2)='$(a)';

NEXT a;

drop tables temp,temp_datetype;

[victor_greffet]

I loaded my data from a qvd file, and after executing the script i have the correct structure but only empty field for dates :

I have some empty field in the source file...

Do you have an idea ?

Thank you again

[victor_greffet]

Very powerfull, I'll keep that in mind thks

[kaanerisen]

It seems the fields don't match for left joins. If you can share a sample of your data and the script, I can look into it.

[victor_greffet]

Hey kaan erisen,

it works perfectly with :

Main:

load distinct

Bridge_Key,

SubField(Type,'_',1) as Step

Resident temp;

left join(Main)

load

Bridge_Key,

SubField(Type,'_',1) as Step,Date as TargetDate

Resident temp

where Type like '*Target*';

left join(Main)

load

Bridge_Key,

SubField(Type,'_',1) as Step,Date as ValidateDate

Resident temp

where Type like '*Validated*';

left join(Main)

load

Bridge_Key,

SubField(Type,'_',1) as Step,Date as ForecastDate

Resident temp

where Type like '*Forecast*';

Drop Table temp;

Thank you again for your precious help