Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have these tables namewith the filed:
I have |
---|
table1_Tmp: ........ table1: LOAD %nameComplete, ..., [Date 1], [Date 2], calc(Date) AS %DurationDate RESIDENT table1_Tmp; JOIN LOAD %nameComplete, [Step Code] FROM $(vfile) (qvd); |
table2_Tmp: .......... table2: LOAD * INLINE %nameComplete; table2: JOIN LOAD %nameComplete, [Date Creation], ... RESIDENT table2_Tmp; |
I would like to do some operations using [Date Creation] and [Date 2] and [Step Code] and %DurationDate:
I tried to do:
I tried to do: |
---|
table2_Tmp: .......... table2: LOAD * INLINE %nameComplete; table2: JOIN LOAD %nameComplete, [Date Creation], ... RESIDENT table2_Tmp; OUTER JOIN LOAD [Date 1], [Date 2], %DurationDate, [Step Code], AVG(if([Step Code] = 5, num( [Date 2] ) - num( [Date Creation] ))) AS %PhaseCreation RESIDENT table1; |
But It doesn't work.
Do you know how i can une my date fields from the table1 in my table2 for the operations ?
Thanks bt advance
Hi.
It seems you don't have [Date Creation] field in table1.
No i know, it's why i would like to join table1 to table2, to use the both fields to make my operations.
How i can do that?
Thanks by advance
In QV you can operate only fields that exists in source table.
There are also way to use values from other tables (including result table) with peek and lookup functions.
To join one table to another use this kind of syntax:
join(TableNameJoinTo)
LOAD
*
Resident TableNameWhatToJoin;
Ok, i tried to do the join bu it still doesn't work for using the [Date Creation].
I'm trying to do:
table2: JOIN LOAD %nameComplete, [Date Creation], ... RESIDENT table2_Tmp; JOIN(table2) LOAD %nameComplete, [Date 1], [Date 2], %DurationDate, [Step Code], AVG(if([Step Code] = 5, num( [Date 2] ) - num( [Date Creation] ))) AS %PhaseCreation RESIDENT table1; |
But the line:
AVG(if([Step Code] = 5, num( [Date 2] ) - num( [Date Creation] ))) AS %PhaseCreation
make me some problems ...
[Date Creation] doesn't exist, but i'm joining the table to the table where [Date Creation] exist ... so I don't understand.
Thanks by advance
In QV you can operate only fields that exists in source table.
This means that [Date Creation] should already be in table1.
You didn't perform the join before as its a wrong syntax:
table2:
JOIN LOAD
should be:
JOIN(table2) LOAD
Thanks for the answer.
I do now JOIN(table2) LOAD for all my join tables. But it doesn't change the problem.
Unfortunatly, i can't put [Date Creation] in table1, that's why i want to make a relation between these 2 tables to have all dates together.
So i don't know how to do that, I think it's with the join but it doesn't work.
The join always works. Some times not as expected...
Consider the last step that you're trying to accomplish.
The source table is table1.
So before this load the field [Date Creation] should be in table1.
This can be done with this kind of script:
join(table1)
LOAD
...some key fields to perform join properly,
representing the relation between Date Creation and table1...
[Date Creation]
Resident ...;
Unfortunatly, i can't put [Date Creation] in table1
If you know the relation. Why ?
You can drop the field from table later (drop field [FieldName] from [TableName];).
There is a good explanation of joins in QV help.
Okey, I can't put [Date Creation] in table1 because it's another data ffile i charge. And i have to charge this data file after the other one.
But thanks i will try to use:
You can drop the field from table later (drop field [FieldName] from [TableName];).