Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
victor_greffet
Partner - Contributor III
Partner - Contributor III

Join Crosstable

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:

IDStep 1
TargetDate
Step 1
ValidateDate
Step 1
ForecastDate
Step 2
TargetDate
Step 2
ValidateDate
Step 2
ForecastDate
.....

Step 5

ForecastDate

Nodatedatedatedatedatedate.....date
Nodatedatedatedatedatedate.....date
Nodatedatedatedatedatedate.....date
No..............................

what I want :

IDSTEPTargetDatesValidateDatesForecastDates
Nostepdatedatedate
Nostepdatedatedate
..................


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 :


Capture.PNG



Thank you for your help!

1 Solution

Accepted Solutions
kaanerisen
Creator III
Creator III

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;

crossJoin.png

View solution in original post

8 Replies
eduardo_dimperio
Specialist II
Specialist II

     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
Creator III
Creator III

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;

crossJoin.png

victor_greffet
Partner - Contributor III
Partner - Contributor III
Author

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

kaanerisen
Creator III
Creator III

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
Partner - Contributor III
Partner - Contributor III
Author

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

Capture.PNG

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

Do you have an idea ?

Thank you again

victor_greffet
Partner - Contributor III
Partner - Contributor III
Author

Very powerfull, I'll keep that in mind thks

kaanerisen
Creator III
Creator III

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
Partner - Contributor III
Partner - Contributor III
Author

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