Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
I have a table which has some 50 fields.
A:
Vendor Version Region State Amount
A 1 EAST AP 140
A 2 WEST KA 150
A 3 SOUTH MP 144
AB 1 EAST AP 140
AB 2 WEST KA 150
AB 3 SOUTH MP 144
I wanted to get an output which looks something like this
B:
VENDOR VERSION FIELD OLD NEW
A 1 REGION - EAST
A 2 REGION EAST WEST
A 3 REGION WEST SOUTH
A 1 STATE - AP
AB 1 REGION - EAST and so on..
The old Column is nothing but the previous value of the given Field and New Column is nothing but the current value of the Field.
I could get the desired result using concatenate.I created a table with the needed structure and started concatenating the values for each column A,but this way is only effective if the number of columns is less
If the number of columns in table A increases to 50 or 100 concatenating is not an efficient way of doing it.
So I wanted to create a loop which takes the column name and its value to give the needed result.
Can someone help me with the looping?
Regards
Rahul
try this
Data:
load * Inline [
Vendor,Version, Region, State, Amount
A, 1, EAST, AP, 140
A , 2, WEST, KA, 150
A, 3, SOUTH, MP, 144
AB, 1, EAST, AP, 140
AB, 2, WEST , KA, 150
AB, 3, SOUTH, MP, 144 ];
New:
CrossTable(Field,Value,3)
LOAD Vendor,
Version,
Amount,
Region,
State
Resident Data;
DROP Table Data;
Final:
LOAD Vendor,
Version,
Amount,
Field,
Value as NEW,
if(Vendor=Previous(Vendor) and Field=Previous(Field),Previous(Value)) as OLD
Resident New
Order by Vendor,Field,Version;
DROP Table New;
Hi Rahul,
You can create loop like Below:
The function you have for converting the string '01/01/2012' into a date is incorrect - the function Date is used for formatting a date field, whilst the function you require, Date#, converts a string to a number.
You could change the line to read:
FOR i=Date#('01/01/2012','DD/MM/YYYY') to $(y)
The suffix in the QVD file name if this worked, would be the integer number for the day (ie. today is 41372), and you probably want it in a YYYYMMDD format.
Try this syntax instead:
let vDays = num#(today() - date#('01/01/2012','DD/MM/YYYY'));
FOR i = 0 to vDays
let vSuffix = Date(today() - vDays + i, 'YYYYMMDD');
TRACE $(i) - $(vSuffix);
NEXT i
You can use $(vSuffix) in the same way as you do $(i) in your code above.
for More Help
THanks,
Arvind Patil
Use crosstable load
Where did you copy paste this from?
It's irrelevant to the query !
May be create some thing like below
Fact:
LOAD Vendor, Version, Region, State, Amount from Fact;
Left Join (Fact)
Load *, <Logic for FIELD> as FIELD;
Left Join (Fact)
Load *, <Logic for OLD> as OLD;
Left Join (Fact)
Load *, <Logic for NEW> as NEW;
// Do here the Generic Key word
Final:
NoConcatenate
Generic Load * Resident Fact;
Drop Table Fact;
Hi,
this solution is useful if the number of fields is less but if you have 100 or 200 fields to do the join from then it will be a tedious task to write so many joins.
That is why i was thinking of some loop to loop through fields.
Regards,
+ its.anandrjs may helps you
post a sample that best represents your scenario
Read some threads on the missing data might be helpful for you.
Regards
Anand
try this
Data:
load * Inline [
Vendor,Version, Region, State, Amount
A, 1, EAST, AP, 140
A , 2, WEST, KA, 150
A, 3, SOUTH, MP, 144
AB, 1, EAST, AP, 140
AB, 2, WEST , KA, 150
AB, 3, SOUTH, MP, 144 ];
New:
CrossTable(Field,Value,3)
LOAD Vendor,
Version,
Amount,
Region,
State
Resident Data;
DROP Table Data;
Final:
LOAD Vendor,
Version,
Amount,
Field,
Value as NEW,
if(Vendor=Previous(Vendor) and Field=Previous(Field),Previous(Value)) as OLD
Resident New
Order by Vendor,Field,Version;
DROP Table New;