Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

victor_greffet
New 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
Contributor III

Re: Join Crosstable

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

8 Replies
eduardo_dimperi
Valued Contributor II

Re: Join Crosstable

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

Re: Join Crosstable

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
New Contributor III

Re: Join Crosstable

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

kaanerisen
Contributor III

Re: Join Crosstable

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
New Contributor III

Re: Join Crosstable

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
New Contributor III

Re: Join Crosstable

Very powerfull, I'll keep that in mind thks

kaanerisen
Contributor III

Re: Join Crosstable

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
New Contributor III

Re: Join Crosstable

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

Community Browser