DSXchange

Hello, We have a requirement to transpose data as follows: Column1 Column2 Column3 Column4 Column5 Row1 a b c 1 1000 Row2 a b c 2 2000 Row3 a b c 3 3000 We need to transpose the input and produce: Column1 Column2 Column3 1 2 3 Row1 a b c 1000 2000 3000 The values present in Column4('1','2','3') beco...

If all of your groups have the first 5 key columns in common (always the same first 5), set your partitioning to Hash (on the two sorts) and select only those 5 columns as your partition keys. Set partitioning to Same on the two transformers. You should be ok to leave partitioning set to Auto elsew...

(sort and join stages, not sorter and joiner stages...different product) Yes you are right. How have you partitioned the data? All the stages have partition set to Auto and I am not explicitly setting partition in any stage. Are you joining on ONLY the "common column", or the key columns ...

Hello, We have a requirement where we need to find the first record and last record in a group based on a certain number of key columns. We have a column generator stage to generate a column with a unique value for each row. We feed the data into two sorters in parallel. One of the sorters sorts the...

Hello, We have a requirement where we need to sort on 10 keys, then remove duplicates based on the first 8 keys out of the 10 keys and then join based on the first 9 keys out of the 10 keys. We have two sorter stages and then a join stage but the partition method chosen seems to not give us the righ...

Thanks for the replies. I used the DateFromDaysSince() function and made the offset as 366. This works perfect for dates less than 02/29/12 but for dates greater than and equal to 02/29/12 the offset should be 365. One option would be checking each date to be less or greater than 02/29/12 but I beli...

Hello, We have two date fields, D1 and D2 , representing start and end dates. According to business rules, we need to increase the year of the dates by one but keep the same day and month. If the year increment results in an invalid date, we make the date field null or store any default value. For e...

Thanks Pandeesh. Let me think on your suggestion. I must add that I need perform the same operation on two other columns from the two flat files also. So extending our earlier example: Flat file (F1) with four columns C1,C2, C3 and C4 C1 C2 C3 C4 1 B C D 2 C Null B 3 Null D C 4 D B Null Flat file (F...

Yes, Ray. I was thinking after my post and a lookup might not really work. Your code is fine but we need the value of D2 corresponding to D1 having a particular value. Sure Pandeesh. Here's the example: Flat file (F1) with two columns C1 and C2 C1 C2 1 B 2 C 3 Null 4 D Flat file (F2) with two column...

Hello, We have a flat file, F1 with say 2 columns, C1 and C2. We have another flat file F2 with two columns D1 and D2. If C2 is null, then we need to replace the null value with the value corresponding to D2 when D1 equals a particular value, say a. The SQL code is Update F1 set C2 = (select D2 from...

Two Sort Stages were implemented. The first sort stage used 20 columns and the input to this Sort Stage was partitioned on the first 15 columns of the 20 columns. The partition was preserved for the second Sort Stage where the first 18 columns (out of 20) were used as the Sort keys. The output seems...

Thanks James. I have made the changes suggested and will provide an update. So, one of the methods to preserve partitioning is to hash partition on the required key columns and then set Partitioning in subsequent stages to same and also set the Preserve Partitioning tab(under Stage tab) to Set. Am I...

So, what columns did you partition your data on for the first sort ? Partitioning option on the first sort was set to Auto. Have you disabled sort insertion and partition insertion for the job (APT_NO_SORT_INSERTION=1 and APT_NO_PART_INSERTION=1)? No. I will set these parameters. Set the partitioni...

Thanks James. We had a Sort Stage and sorted the data based on 20 columns. We then used a second Sort Stage immediately downstream of the first Sort Stage and sorted the data based on the first 18 columns of the previous 20 columns and set the Allow Duplicates option to False. We get the first recor...

Hello, We need to sort a file based on say n number of columns. We then need to select the first record from each group sorted on n-2 columns. So, we initialy sort the file based on 20 columns (using a sort stage) and maybe use another sort stage or remove duplicates stage to select the first record...