topic Re: Join Crosstable in App Development

Join Crosstable

victor_greffet — Tue, 03 Oct 2017 13:29:03 GMT

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!

Re: Join Crosstable

eduardo_dimperio — Tue, 03 Oct 2017 16:57:37 GMT

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?

Re: Join Crosstable

kaanerisen — Wed, 04 Oct 2017 06:02:57 GMT

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;

Re: Join Crosstable

victor_greffet — Wed, 04 Oct 2017 06:50:04 GMT

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

Re: Join Crosstable

kaanerisen — Wed, 04 Oct 2017 07:14:05 GMT

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;

Re: Join Crosstable

victor_greffet — Wed, 04 Oct 2017 07:43:00 GMT

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

Re: Join Crosstable

victor_greffet — Wed, 04 Oct 2017 07:55:27 GMT

Very powerfull, I'll keep that in mind thks

Re: Join Crosstable

kaanerisen — Thu, 05 Oct 2017 04:33:59 GMT

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.

Re: Join Crosstable

victor_greffet — Thu, 05 Oct 2017 11:18:02 GMT

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